none
Select from another table in case statement RRS feed

  • Question

  • Hi,

    select
    , case when (select t2.col from table2 t2 where t2.id = t1.id) is not null then t2.col else null end as 'Col'
    , case when (select t2.col from table2 t2 where t2.id = t1.id) is not null then t2.col2 else null end as 'Col2'
    from table1 t1

    Is there a way to select t2.col in first case statement with out repeating select statement again? And For both cases, condition is same is there a way of doing without repeatition. Can't use join of t2 with t1 outside.

    TIA

    Tuesday, January 14, 2020 7:52 PM

All replies

  • SELECT t2.Col1, t2.Col2

    FROM Table1 AS t1 

    INNER JOIN Table2 AS t2 ON t2.Id = t1.Id


    A Fan of SSIS, SSRS and SSAS

    Tuesday, January 14, 2020 8:00 PM
  • select t1.id,
      t2.col  as 'Col'
    , t2.col2  as 'Col2'
    from table1 t1 
    left join table2 t2 ON t2.id = t1.id

    Tuesday, January 14, 2020 9:33 PM
    Moderator
  • CROSS or OUTER APPLY:

    select
    , c.col
    , c.col2
    from table1 t1
    outer apply (select t2.col, t2.col2 from table2 t2 where t2.id = t1.id) As c
    


    Jeff Williams

    Tuesday, January 14, 2020 10:13 PM
  • Hi bluepink,

    Or please try to use CROSS APPLY. In this example , you might find that the result of INNER JOIN is equal to CROSS APPLY's and the result of LEFT OUTER  JOIN is equal to OUTER APPLY's . 

    Please check .

    SELECT  t2.col  as 'Col', t2.col2  as 'Col2'
    FROM table1 t1 CROSS APPLY (select * from table2 where id=t1.id) t2

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 15, 2020 3:39 AM