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

join, left-join, mysql, sql

Solution

Try Like This

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 Thanks @Phil

Problem

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 the sales_cat rows, joined with the user_cat table for a specific user, indicating if that user has or not the category. 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 linked record in user_cat. I've set a table structure in sqlfiddle. My current solution only returns the linked 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 is returning: ``` id_cat | name 1 | kids 2 | men ``` I'm still missing the "selected" column and the 3 | women row. Any ideas? Thanks!

Original source