CODEWITHSUNDEEP
sqlsql-servert-sql
SVI
SVI

Reputation: 951

Selecting from table into a column

I have a (bit type) column named Status in table1 which indicates to 0 = Stopped, 1 = Started and there's a log table that has the ID of table1, the row existence indicates that the Status in table1 is Damaged.

How can I create a select statement that covers the three statuses? I was thinking of creating a temp table that has all the selected columns plus to a new column (NewStatus) that gets it's value from Status column and a subselect statement that filter table2 for the row ID in table1. But I can't imagine how I can do that!

Is there any better approach?

Upvotes: 0

Views: 48

Answers (1)

podiluska
podiluska

Reputation: 51514

Using a left join

select table1.*, 
   case when table2.id is null then 
        case table1.status 
        when 0 then 'stopped'
        when 1 then 'started'
        end
   else 'damaged'
   end
from
   table1 left join table2 on table1.id = table2.id

You could probably use isnull to obscure your intent which may or may not be faster.

Upvotes: 1

Related Questions