February 4, 2014 at 11:08 am
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
February 4, 2014 at 12:16 pm
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.
February 4, 2014 at 1:08 pm
Are you using the SELECT * or is that just for the example? If not you might want to list the desired columns.
February 4, 2014 at 1:41 pm
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...
February 4, 2014 at 2:26 pm
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 6, 2014 at 4:14 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply