Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

AVOID JOINING ON TO THE SAME TABLE MULTIPLE TIMES? Expand / Collapse
Author
Message
Posted Tuesday, February 4, 2014 11:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:24 AM
Points: 41, Visits: 108
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

Post #1537882
Posted Tuesday, February 4, 2014 12:16 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:47 AM
Points: 624, Visits: 322
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.
Post #1537910
Posted Tuesday, February 4, 2014 1:08 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:21 PM
Points: 196, Visits: 712
Are you using the SELECT * or is that just for the example? If not you might want to list the desired columns.
Post #1537925
Posted Tuesday, February 4, 2014 1:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:24 AM
Points: 41, Visits: 108
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...
Post #1537934
Posted Tuesday, February 4, 2014 2:26 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:14 PM
Points: 6,171, Visits: 7,237
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
Post #1537947
Posted Thursday, February 6, 2014 4:14 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:48 PM
Points: 1,973, Visits: 2,919
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1538938
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse