SQL Query for CASE Statement

Question

I have two table one is master table and another table is you can select item from first table.

MasterTable

ItemID      ItemName
1           Football
2           Cricket
3           Badminton 

SelectionTable

UserID     SelectedItemId
1            2
1            3
2            1

OutPut

UserId      SelectedItemID     SelectionStatus
1            1                   False
1            2                   True
1            3                   True

Query

SELECT  S.UserId,M.ItemID,
        CASE M.ItemID 
            WHEN 1 Then 'True'
            WHEN 2 Then 'True'
            WHen 3 Then 'True' END AS SelectionStatus
From MasterTable M
JOIN SelectionTable S ON S.SelectedItemID=M.ItemID      
WHERE S.UserId=1

If no any item selected then all are false.I don't know how to do.

Answer 1

Assuming that you have a User table, you can get the status of every user / item combination by querying cartesean product (cross join).

The status True or False can be determined the presence or absence of a corresponding record in the SelectionTable.

select
    u.UserId,
    m.ItemId,
    case
        when exists
            (select *
            from SelectionTable s
            where s.UserId = u.UserId
              and s.SelectedItemId = m.ItemId)
        then 'True'
        else 'False'
    end
from MasterTable m, User u

This technique can be applied to the single user case (UserId equals 1) as follows:

select
    m.ItemId,
    case
        when exists
            (select *
            from SelectionTable s
            where s.UserId = 1
              and s.SelectedItemId = m.ItemId)
        then 'True'
        else 'False'
    end
from MasterTable m

Answer 2

You can use else and a left join from SelectionTable to MasterTable.

SELECT  S.UserId,M.ItemID,
        CASE M.ItemID 
            WHEN 1 Then 'True'
            WHEN 2 Then 'True'
            WHen 3 Then 'True' 
            else 'False'
            END AS SelectionStatus
From SelectionTable S
  left JOIN MasterTable M ON S.SelectedItemID=M.ItemID      
WHERE S.UserId=1