October 27, 2017 at 1:03 am
Hi Experts,
How can i accomplish this using case within select.
Please see my sample below.
select A.name
,(Case A.id=2 then select C.ID from TableThree C left join A.id=C.id end)
from TableOne A
left join TabletTwo B
on A.id=B.id
best regards
October 27, 2017 at 1:49 am
I wouldn't nest a select statement like that.
Rather do another outer join:
select A.name
,(Case WHEN A.id = 2 then C.ID ELSE ??? END)
from TableOne A
left join TabletTwo B
on A.id=B.id
LEFT JOIN TableThree C
ON C.id = A.id
Also, replace the "???" with whatever value you want if A.id doesn't = 2
But if you absolutely needed to:
select A.name
,(SELECT Case WHEN A.id = 2 then C.ID ELSE ??? END FROM TableThree C left join TableOne A ON A.id = C.id )
from TableOne A
left join TabletTwo B
on A.id=B.id
October 27, 2017 at 1:59 am
select A.name, case when A.id=2 then C.ID else null end as [ID]
from TableOne A
left join TabletTwo B on A.id=B.id
left join TableThree C on A.id=C.id
or:
select A.name, C.ID -- C.ID will be NULL unless a.id = 2
from TableOne A
left join TabletTwo B on A.id=B.id
left join TableThree C on A.id=C.id AND a.id = 2
or
declare @C_ID int = (SELECT ID FROM TableThree WHERE id = 2); -- will be '2' if row found, NULL if not found
select A.name, case when a.id = 2 then @C_ID else NULL end as [c_id]
from TableOne A
left join TabletTwo B on A.id=B.id
Eddie Wuerch
MCM: SQL
November 3, 2017 at 1:51 pm
There are actually a couple more options, but the one that you were likely looking for is called a correlated subquery. The subquery can reference values from the outer query, so there is no need for a join. (NOTE: This query will produce an error if there are multiple rows in TableThree that meet the criteria.)
select A.name
,(select C.ID from TableThree C WHERE A.id=C.id AND A.id=2)
from TableOne A
left join TabletTwo B
on A.id=B.id
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy