Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


AVOID JOINING ON TO THE SAME TABLE MULTIPLE TIMES?


AVOID JOINING ON TO THE SAME TABLE MULTIPLE TIMES?

Author
Message
James Millar-305032
James Millar-305032
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 118
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
SQLBlimp
SQLBlimp
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 495
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.
djj
djj
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1791 Visits: 3285
Are you using the SELECT * or is that just for the example? If not you might want to list the desired columns.
James Millar-305032
James Millar-305032
SSC Rookie
SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)SSC Rookie (47 reputation)

Group: General Forum Members
Points: 47 Visits: 118
Thanks guys...

My select * was just an example. I was actually selecting using a coalesce - COALESCE( C.ClassName_FD, C2.ClassName_FD, '' ) etc. Basically getting the class from the join where there is an airline first and then the join with no airline next.

I can use an inner join with the "OR" however I get duplicate rows in my query - it seems to return once with the airline and again without the airline.

I was hoping there could be a way to wrap the two class_tb joins using a "(SELECT.." or some sort of derived table then with my table joins after this I would only need to join onto Class_TB once as opposed to joining on to Class_TB with the airline and Class_TB without airline.

Hope that makes sense...
Evil Kraig F
Evil Kraig F
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5697 Visits: 7660
Short form: You're pivoting your data. You're going to have to call the data multiple times to selectively choose what you're pivoting with.

Now, you could do a temp table pull of the data to limit what you need to pivot on, but that's probably not going to get you far. You could do an inline PIVOT after the grab with multiple rows, but that's going to get very complex and it's also an expensive operation.

Really, without digging deeply into your structure, indexes, query plans, and the like... you're pretty much stuck with what you've got.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3938 Visits: 6669
You could use subqueries instead of joins, like this:


SELECT
FI.*,
(SELECT TOP (1) C.ClassName_FD
FROM DBA.Class_TB AS C
WHERE C.ClassCode_FD = FI.strCcClassCode_FD
AND (C.Airline_FD = FI.strFiAirlineCode_FD OR C.Airline_FD = '')
ORDER BY C.Airline_FD DESC
) AS ClassName_FD,
...
FROM DBA.FoldItems_TB FI




Edit: You should also be able to use CROSS/OUTER APPLY to do the lookup if you prefer.

SQL DBA,SQL Server MVP('07, '08, '09)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search