Is there a way to make a join with cases?
Something like
select * from abba
case when a=b then
join acdc on abba.id=acdc.id
when b=c then
join bebop on abba.id=bebop.id
end
Is there a way to do this?
Best regards Joe
Is there a way to make a join with cases?
Something like
select * from abba
case when a=b then
join acdc on abba.id=acdc.id
when b=c then
join bebop on abba.id=bebop.id
end
Is there a way to do this?
Best regards Joe
If the query is non-dynamic then no since case
statement only returns a literal value such as string
, int
, or other data-types.
But there is a work-around - dynamic sql. You treat the entire statement as a string. Then you can work your cases inside the string.
For example:
declare @query varchar(1000)
set @query = 'select * from abba '+
case when @a=@b then 'join acdc on abba.id=acdc.id'
when @b=@c then 'join bebop on abba.id=bebop.id'
end
exec(@query)
But ofcourse, your a=b & b=c must be some sql variable.
The usual way to do this is to LEFT JOIN both tables and select the value based on the CASE condition: