How to use inner join inside Select using case?

• jaquino012

  SSC-Addicted

  Points: 431

  More actions

  October 27, 2017 at 1:03 am

  #151131

  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

• Paulo de Jesus

  Default port

  Points: 1403

  More actions

  October 27, 2017 at 1:49 am

  #1965463

  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


• Eddie Wuerch

  SSChampion

  Points: 12541

  More actions

  October 27, 2017 at 1:59 am

  #1965464

  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

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  November 3, 2017 at 1:51 pm

  #1966620

  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