Case in a join?

Last Post 14 Nov 2008 02:04 AM by SwePeso. 1 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

13 Nov 2008 12:46 PM
is it possible to do a case statement in a join. I have an order table which needs to join to a location table. It can join on the order_location
or if the shiptolocation has a value it needs to join instead of the order_location.
New Member
New Member

14 Nov 2008 02:04 AM
No, you can't change the JOIN tables with a CASE statement.
However, you can change JOIN criteria with a CASE statement.

In your case, I would LEFT JOIN the two tables and use CASE statement in the SELECT part.

SELECT s.*, CASE WHEN s.zType = 'out' then t1.ColValue ELSE t2.ColINT END
FROM Source AS s
LEFT JOIN Table1 AS t1 ON t1.Col1 = s.ColX
LEFT JOIN Table2 AS t2 ON t2.Col1 = s.ColY

Acceptable Use Policy