Alright all you SQL gurus, I have a little SQL problem, and would like to seek for help.
In the following code, only the classes with class instructor assigned gets showed. I am looking for classes whether or not instrutor is assigned. So that means a LEFT JOIN between Table A and Table E. But I have lost track of how to handle other keys between these two tables. Any help would be greatly appreciated.
SELECT a.class_nbr
, a.subject
, a.catalog_nbr
, a.class_section
, a.descr
, a.campus
, a.crse_id
, a.crse_offer_nbr
, a.strm
, a.session_code
, a.enrl_cap
, a.enrl_tot
, a.enrl_stat
, b.mon
, b.tues
, b.wed
, b.thurs
, b.fri
, b.sat
, b.sun
, b.meeting_time_start
, b.meeting_time_end
, b.facility_id
, d.room_chrstc
, e.emplid
, f.last_name
, f.first_name
, a.acad_group
, g.descrshort AS acad_org
FROM lsapro.ps_class_tbl a
, lsapro.ps_class_instr e
, lsapro.ps_personal_data f
, lsapro.ps_ua_acad_org_vw g
, lsapro.ps_class_mtg_pat b LEFT OUTER JOIN lsapro.ps_ua_facil_chr_vw d ON
d.facility_id=b.facility_id
AND d.room_chrstc IN ('30'
, '31'
, '32'
, '33'
, '34')
WHERE a.class_stat <> 'X'
AND b.crse_id=a.crse_id
AND b.crse_offer_nbr=a.crse_offer_nbr
AND b.strm=a.strm
AND b.session_code=a.session_code
AND b.class_section=a.class_section
AND e.crse_id = a.crse_id
AND e.instr_role='PI'
AND e.crse_offer_nbr = a.crse_offer_nbr
AND e.strm = a.strm
AND e.session_code = a.session_code
AND e.class_section = a.class_section
AND e.emplid=f.emplid
AND g.acad_org=a.acad_org