Another Join? 1

[dande]

In the following how can I pull the staff name (first and last) from the staffmaster file instead of staffmasterid_c from the enrollments table (ec,ew & ed)?

select c.uniqueid_c,
c.clientcode_c,
a.type_c as a_type,
a.address1_vc,
a.address2_vc,
a.address3_vc,
a.city_vc,
a.state_c,
a.zip_c,
a.county_vc,
p.type_c as p_type,
p.commentry_vc,
ec.staffmasterid_c as clinician,
ew.staffmasterid_c as caseworker,
ed.staffmasterid_c as doctor
from ar.client as c
left join echo.er_address as a on (c.uniqueid_c = a.linkid_c and a.type_c ='client' and a.enddate_d is null)
left join echo.er_communication as p on (c.uniqueid_c = p.linkid_c and p.type_c ='client' and p.enddate_d is null)
left join cd.enrollments as ec on (c.uniqueid_c = ec.clientid_c and ec.program_c = '500' and ec.enddate_d is null and (ec.ru_c is null or ec.ru_c = 'wrkr'))
left join cd.enrollments as ew on (c.uniqueid_c = ew.clientid_c and ew.program_c = '100' and ew.enddate_d is null and (ew.ru_c is null or ew.ru_c = 'wrkr'))
left join cd.enrollments as ed on (c.uniqueid_c = ed.clientid_c and ed.program_c = '900' and ed.enddate_d is null and (ed.ru_c is null or ed.ru_c = 'wrkr'))
where c.admstat_c = 'open'

Thanks - Paul

 

[r937]

yes, three more joins

add this to the SELECT --

, ecs.first as clinician_first
, ecs.last as clinician_last
, ews.first as caseworker_first
, ews.last as caseworker_last
, eds.first as doctor_first
, eds.last as doctor_last

then add this to the FROM --

left outer join staffmaster as ecs
  on ec.staffmasterid_c = ecs.id
left outer join staffmaster as ews
  on ew.staffmasterid_c = ews.id
left outer join staffmaster as eds
  on ed.staffmasterid_c = eds.id

rudy

http://r937.com/

SQL Consulting

 

[dande]

Thanks rudy.. I have just begun working with SQL. The joins and nested selects can be confussing some times. You have helped the light get brighter. I was thinking that the joined table had to be directly linked to the main table, ar.client, in this case. I now have a better understanding of using joins. Instead of selecting the 2 name fields in each enrollment I put the 2 fields together and just created a ecmane field. Is this code the most efficient way to get the desired results? Thanks again for the lesson. - Paul