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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply