Sql Outer Join: Extracting values from multible tables.

Question

I am extracting data from multiple tables. mt query is as follows:

SELECT p.Record_Num as RecordNum
,p.GCD_ID as GCDID
,p.Project_Desc as ProjectDesc
,p.Proponent_Name as ProponentName
,st.Station_Name as StationName
,p.OpCentre as OpCentre
,s.Sector_Name as SectorName
,p.PLZone as PLZone
,f.Feeder_Desc as FeederDesc
,d.DxTx_Desc as DxTx
,op.Op_Control_Desc as OpControl
,t.Type_Desc as Type
,c.Conn_Desc as ConnectionKV
,ss.Status_Desc as Status
,p.MW as MW
,p.Subject as Subject
,p.Ip_Num as IpNum
,p.H1N_ID as H1NID
,p.NOMS_Slip_Num as NomsSlipNum
,p.NMS_Updated as NmsUpdated
,p.Received_Date as ReceivedDate
,p.Actual_IS_Date as ActualISDate
,p.Scheduled_IS_Date as ScheduledIsDate
,stst.Station_Name as UpStation
,ff.Feeder_Desc as UpFeeder
,p.HV_Circuit as HVCircuit
,p.SIA_Required as SIAReqd
FROM Project_Detail p,
Station st, Sector s, Feeder f, DxTx d, Operational_Control op, Type t,
Connection_Kv c, Status ss, Station stst, Feeder ff
WHERE 
p.Station_ID = st.Station_ID and
p.Sector_ID = s.Sector_ID and
p.Feeder = f.Feeder_ID and
p.DxTx_ID = d.DxTx_ID and
p.OpControl_ID = op.Op_Control_ID and 
p.Type_ID= t.Type_ID and
p.ConnKV_ID = c.Conn_ID and
p.Status_ID = ss.Status_ID and
p.UP_Station_ID = stst.Station_ID and
p.UP_Feeder_ID = ff.Feeder_ID

The problem with this query is if it doesnot find an associated value in the second table, it doesnot show the row. for example : every project has feeders. so if a project_detail table has a feederid which doesnot have an association in the feeder table, then it wont show the row. also, there are times when the feeders are not assigned to a project.

i think i have to use outer joins to get the values. but i cannot figure out how to do that. please help.

Answer 1

SELECT  *
FROM    Project_Detail p
LEFT JOIN
        Station st
ON      p.Station_ID = st.Station_ID
LEFT JOIN
        Sector s
ON      p.Sector_ID = s.Sector_ID
…

Answer 2

You need LEFT OR FULL OUTER JOINS instead of the inner joins you are now using with your where clause.

SELECT  p.Record_Num as RecordNum
        ,p.GCD_ID as GCDID
        ,p.Project_Desc as ProjectDesc
        ,p.Proponent_Name as ProponentName
        ,st.Station_Name as StationName
        ,p.OpCentre as OpCentre
        ,s.Sector_Name as SectorName
        ,p.PLZone as PLZone
        ,f.Feeder_Desc as FeederDesc
        ,d.DxTx_Desc as DxTx
        ,op.Op_Control_Desc as OpControl
        ,t.Type_Desc as Type
        ,c.Conn_Desc as ConnectionKV
        ,ss.Status_Desc as Status
        ,p.MW as MW
        ,p.Subject as Subject
        ,p.Ip_Num as IpNum
        ,p.H1N_ID as H1NID
        ,p.NOMS_Slip_Num as NomsSlipNum
        ,p.NMS_Updated as NmsUpdated
        ,p.Received_Date as ReceivedDate
        ,p.Actual_IS_Date as ActualISDate
        ,p.Scheduled_IS_Date as ScheduledIsDate
        ,stst.Station_Name as UpStation
        ,ff.Feeder_Desc as UpFeeder
        ,p.HV_Circuit as HVCircuit
        ,p.SIA_Required as SIAReqd
FROM    Project_Detail p
        LEFT OUTER JOIN Station st ON p.Station_ID = st.Station_ID
        LEFT OUTER JOIN Sector s ON p.Sector_ID = s.Sector_ID
        LEFT OUTER JOIN Feeder f ON p.Feeder = f.Feeder_ID
        LEFT OUTER JOIN DxTx d ON p.DxTx_ID = d.DxTx_ID
        LEFT OUTER JOIN Operational_Control op ON p.OpControl_ID = op.Op_Control_ID 
        LEFT OUTER JOIN Type t ON p.Type_ID= t.Type_ID
        LEFT OUTER JOIN Connection_Kv c ON p.ConnKV_ID = c.Conn_ID
        LEFT OUTER JOIN Status ss ON p.Status_ID = ss.Status_ID
        LEFT OUTER JOIN Station stst ON p.UP_Station_ID = stst.Station_ID
        LEFT OUTER JOIN Feeder ff ON p.UP_Feeder_ID = ff.Feeder_ID