Sql Outer Join : Extraire des valeurs de plusieurs tables

Question

Je suis en train d'extraire des données de plusieurs tables. Ma requête est la suivante :

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

Le problème avec cette requête est que si elle ne trouve pas de valeur associée dans la deuxième table, elle ne montre pas la ligne. par exemple : chaque projet a des feeders. donc si une table project_detail a un feederid qui n'a pas d'association dans la table feeder, alors il ne montrera pas la ligne. aussi, il y a des fois où les feeders ne sont pas assignés à un projet.

Je pense que je dois utiliser des jointures externes pour obtenir les valeurs, mais je ne sais pas comment faire. Aidez-moi, s'il vous plaît.

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

Vous devez LEFT OR FULL OUTER JOINS au lieu des jointures internes que vous utilisez maintenant avec votre clause where.

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