CODEWITHSUNDEEP
mysqlsql
birdy
birdy

Reputation: 9636

how to join two tables and get null values

I have three tables Color, Shade, and Activity:

Color:

id     color
---    ------
1       red
2       green
3       white



Shade

   id      shade    
  ----    ------- 
     1      light    
     2        dark

Activity:

user_id  shade_id  color_id
------   --------  --------
1          1        1
1          1        2
2          2        3

I am using mysql and can easily find the colors belonging to a user by a specific shade:

select c.name, 'assigned'
from color c
left join activity a on c.id = a.color_id
where a.shade_id = 1 and a.user_id = 1;

The above will give me:

Color  Status
-----  -------
red    assigned
green  assigned

Question

However, I want a list of all the colors with assigned for ones that belong to the her and not assigned for ones that don't.

So I would want

Color       Status
-----       -------
red         assigned
green       assigned
white       not assigned

Upvotes: 0

Views: 72

Answers (4)

user330315
user330315

Reputation:

Just move your schade_id = 1 condition from the where clause to the join condition (ON...)

select c.name, 'assigned'
from color c
  left join activity a 
         on c.id = a.color_id 
        and a.shade_id = 1;

Upvotes: 0

Eran Boudjnah
Eran Boudjnah

Reputation: 1268

SELECT c.name, 'assigned'
FROM Color c
LEFT JOIN Activity a
ON c.id = a.color_id
WHERE a.shade_id = 1 AND a.user_id = 1
UNION
SELECT c.name, 'unassigned'
FROM Color c
WHERE c.id NOT IN (
  SELECT a.color_id
  FROM Activity a
  WHERE a.shade_id = 1 AND a.user_id = 1);

Or, merging the two other answers (I liked both more than mine, to be honest):

SELECT Color, CASE WHEN B.shade_id IS NULL THEN 'Not Assigned'
ELSE 'Assigned'
END AS Status
FROM Color A
LEFT JOIN
(SELECT * FROM Activity WHERE shade_id = 1) B
ON A.id = B.color_id;

Upvotes: 0

Rahul
Rahul

Reputation: 77876

Join it with shade table as well, like below

select name,
case when shade is null then 'Not Assigned'
else 'Assigned'
end as Status
from
(select c.name, s.shade
from color c
left join shade s on c.id = s.id
left join activity a on c.id = a.color_id
where a.shade_id = 1 and a.user_id = 1;
) tab

Upvotes: 1

Yosi Dahari
Yosi Dahari

Reputation: 6999

SELECT Color, B.shade_id Status
FROM Color A LEFT JOIN (SELECT * 
                      FROM Activity WHERE shade_id = 1) B
     ON A.id = B.color_id

NULL represents unassigned.

Upvotes: 1

Related Questions