MySQL: return all rows of table A and true | false if the record exists in table B

Question

I have a sales_cat table and a user_cat table.

sales_cat

• id_cat
• name

user_cat

• ID
• id_user
• id_cat

I need to get all sales_cat rows connected to the user_cat table for a specific user, indicating whether this user has a category or not. For example, for id_user = 4 it should return:

id_cat | name  | selected
1      | kids  | 1
2      | men   | 1
3      | women | 0

Of course, the "selected" field is actually a value that depends on the existence of a related record in user_cat. I set the table structure in sqlfiddle .

My current solution returns only related data:

SELECT sales_cat.id_cat, sales_cat.name
FROM sales_cat
LEFT JOIN user_cat ON user_cat.id_cat = sales_cat.id_cat
WHERE user_cat.id_user = 4

... which returns:

id_cat | name
1      | kids
2      | men

I'm still missing the "selected" column and 3 | women .

Any ideas? Thank!

Answer 1

SELECT sales_cat.id_cat, sales_cat.name,case when
user_cat.id is null then 0 else 1 end as "selected" FROM 
sales_cat LEFT JOIN user_cat ON 
user_cat.id_cat = sales_cat.id_cat and 
user_cat.id_user = 4 

http://sqlfiddle.com/#!2/395ba/25 @Phil

Answer 2

:

select distinct 
s.id_cat, 
s.name, 
case when u.id_user is null then 0 else 1 end selected
from sales_cat s
left join user_cat u on s.id_cat = u.id_cat 
and (u.id_user = 4 or u.id_user is null)

Satson, user_cat id_user.

Answer 3

:

SELECT sales_cat.id_cat, sales_cat.name, , user_cat.id_user , 1 else 0 end selected FROM sales_cat LEFT JOIN user_cat ON (user_cat.id_cat = sales_cat.id_cat user_cat.id_user = 4)