CODEWITHSUNDEEP
mysqlsql
user1111929
user1111929

Reputation: 6099

Can this be done in a single SQL query?

I have a table lists with columns list_id, name, etc. I have another table members with columns user_id and list_id (with a unique index on the pair (user,list)).

Now, I want to generate a three-column output: list_id, name, membership where membership is 0 or 1 depending on whether or not the current user is member of the list (i.e. there is an entry for that user on that list). If I do 

SELECT 
  list_id, name, 1 
FROM 
  lists 
  LEFT JOIN members ON (lists.list_id = members.list_id AND members.user_id=2)

I will get the correct 1-rows for user 2, but the 0-rows will simply be gone. Is there a nice way to obtain my desired effect with a single MySQL query?

Upvotes: 0

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

I think you want something like this:

SELECT list_id, name, (m.list_id is not null) as MemberOnList
FROM lists l LEFT JOIN
     members m
     ON l.list_id = m.list_id and
        m.member_id = @CURRENTUSER;

Upvotes: 3

Related Questions