CODEWITHSUNDEEP
mysql
user5779223
user5779223

Reputation: 1490

How to select the groups which a certain user doesn't join with Mysql?

Given three data table, theUser, theGroup, membership:

theUser:(*user_id*, username),
theGroup:(*group_id*, groupname),
membership:(*group_id*, *user_id*)

For instance, group 2 has user 007 and 008, the membership should be like:

group_id      user_id
2             007
2             008

Now I wish to select the groups that user 008 HAS NOT BEEN IN. Could you tell me how to do that? Thanks in advance!

Upvotes: 0

Views: 38

Answers (3)

Azra
Azra

Reputation: 48

SELECT * FROM theGroup WHERE group_id NOT IN(SELECT group_id FROM membership WHERE user_id LIKE '008')

Upvotes: -1

Priyansh Goel
Priyansh Goel

Reputation: 2660

Try this:

 select distinct group_id 
 from theGroup 
 where group_id not in 
(select group_id from membership where user_id='008');

Upvotes: 1

sgeddes
sgeddes

Reputation: 62851

Several different ways to do this. Here's one with an outer join / null check:

select g.*
from thegroup g
   left join membership m on g.groupid = m.groupid and m.userid = '008'
where m.groupid is null

Upvotes: 2

Related Questions