Shaggy Shaggy - 7 months ago 26
SQL Question

Get all the records from left table with computed column value

I have master table with structure :


ID Name
1 Cricket
2 Football
3 Golf


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

is foreign ket fot table T1, ID column. I want to get resultset for value in Somenumber for

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.


You can use left join:

select t1.*,
       (case when is null then 'False' else 'True' end) as ComputedColumn
from t1 left join
     on = 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 = t2.t1_id and t2.somenumber = 180)
             then 'True' else 'False'
        end) as ComputedColumn
from t1;