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

conditional joins in select statement Expand / Collapse
Author
Message
Posted Wednesday, February 19, 2014 5:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, February 20, 2014 7:28 AM
Points: 75, Visits: 197
SELECT t1.id,name, from table1 t1 INNER join table t2 on t1.id=t2.pid
case when type='A' then
Inner join table3 t3 on t1.id=t3.id
else
Inner join table4 t4 on t1.id=t4.id
END
Post #1542937
Posted Wednesday, February 19, 2014 5:44 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 12,197, Visits: 9,150
That's not possible in SQL.
You need to write two seperate queries and choose between them using an IF statement, or you construct your query dynamically and execute it with sp_executesql.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1542941
Posted Wednesday, February 19, 2014 6:53 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:20 AM
Points: 982, Visits: 771
As Koen mentioned that is not possible in SQL and should explore the options he mentioned.
Probably one more option if i understand your question correctly is to do a LEFT JOIN as below. Try if this helps...
SELECT T1.Id,T2.Name,
CASE WHEN T1.Type = 'A' THEN T3.SomeColumn
ELSE T4.SomeColumn
END AS SomeColumn
FROM Table1 AS T1
INNER JOIN Table2 AS T2 ON T1.Id = T2.Pid
LEFT OUTER JOIN Table3 AS T3 ON T1.Id = T3.Id AND T1.Type = 'A'
LEFT OUTER JOIN Table4 AS T4 ON T1.Id = T4.Id AND T1.Type <> 'A'

Post #1542976
Posted Sunday, February 23, 2014 6:14 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:31 AM
Points: 3,590, Visits: 5,097
Perhaps something like this might work for you:

SELECT t1.id,name 
FROM table1 t1
INNER JOIN table t2 ON t1.id=t2.pid
INNER JOIN table3 t3 on t1.id=CASE type WHEN 'A' THEN t3.id ELSE t1.id END
INNER JOIN table4 t4 on t1.id=CASE type WHEN 'A' THEN t1.id ELSE t4.id END





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1544335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse