Shaggy Shaggy - 2 months ago 6
SQL Question

Get all the records from left table with computed column value

I have master table with structure :

T1

ID Name
1 Cricket
2 Football
3 Golf


T2

ID T1-ID SomeNumber
1 1 180
2 2 180
3 1 195
4 3 195


Column
T1-ID
is foreign ket fot table T1, ID column. I want to get resultset for value in Somenumber for
'180'
like

Output :

ID Name ComputedColumn
1 Cricket True
2 Football True
3 Golf False


I have tried using Left-Joins, Case Statements but didnt get expected results.

Answer

You can use left join:

select t1.*,
       (case when t2.id is null then 'False' else 'True' end) as ComputedColumn
from t1 left join
     t2
     on t1.id = t2.t1_id and t2.somenumber = 180;

Actually, that assumes that a row with 180 occurs at most once for each id (as in your sample data). If such rows could occur multiple times, use case:

select t1.*,
       (case when exists (select 1 from t2 where t1.id = t2.t1_id and t2.somenumber = 180)
             then 'True' else 'False'
        end) as ComputedColumn
from t1;