James Millar-305032 (2/4/2014)
Hi there,I need to join onto a table (Class_TB) twice because one of the conditions (Airline_FD) could be NULL, so there are 2 LOJ's but this has a domino effect and leads to multiple joins onto both tables in my view. Is there a way I can combine the Class_TB table joins so instead of joining other tables onto both joins I can amalgamate (not sure that's the right word!) them.
Here is an example of what I am trying to do - there are more tables but I just included the first few so you get the idea...
SELECT * FROM DBA.FoldItems_TB FI
LEFT OUTER JOIN DBA.Class_TB AS C
ON C.ClassCode_FD = FI.strCcClassCode_FD
AND C.Airline_FD = FI.strFiAirlineCode_FD
LEFT OUTER JOIN DBA.Class_TB AS C2
ON C2.ClassCode_FD = FI.strCcClassCode_FD
AND C2.Airline_FD = ''
LEFT OUTER JOIN DBA.ClassAutoRemarkAssociationDocTypesEdObjs_VW AS CAR
ON CAR.ClassCode_FD = C.ClassCode_FD
AND CAR.Airline_FD = C.Airline_FD
LEFT OUTER JOIN DBA.ClassAutoRemarkAssociationDocTypesEdObjs_VW AS CAR2
ON CAR2.ClassCode_FD = C2.ClassCode_FD
AND C2.Airline_FD = ''
LEFT OUTER JOIN DBA.ClassAutoRemarkDetails_TB AS CARD
ON CAR.ClassCode_FD = CARD.ClassCode_FD
AND CAR.Airline_FD = CARD.Airline_FD
AND CAR.nCarAutoRemarkID_FD = CARD.nCarAutoRemarkID_FD
LEFT OUTER JOIN DBA.ClassAutoRemarkDetails_TB AS CARD2
ON CAR.ClassCode_FD = CARD2.ClassCode_FD
AND CAR.Airline_FD = ''
AND CAR.nClassAutoRemarkID_FD = CARD2.nClassAutoRemarkID_FD
Why can't you do this?
LEFT OUTER JOIN DBA.Class_TB AS C
ON C.ClassCode_FD = FI.strCcClassCode_FD
AND
(
C.Airline_FD = FI.strFiAirlineCode_FD
or C2.Airline_FD is NULL
or C2.Airline_FD = ''
)
Are you trying to LOJ to CLASS_TB when C.ClassCode_FD = FI.strCcClassCode_FD and C.Airline_FD is one of FI.strFiAirlineCode_FD, empty string or NULL? That is what it reads like. If yes, can you INNER JOIN to the table on that condition set? Otherwise you will get all of the rows of FOLDITEMS_TB, with nulls if the row matches nothing.
Perhaps you could give a detailed clarification of your intended result.
Thanks
John.