CODEWITHSUNDEEP
mysqlsqlsubqueryleft-joininner-join
Dzhuneyt
Dzhuneyt

Reputation: 8691

Left Join, But Exclude If Joined Table Contains Entry

I know the title seems trivial, but the problem is a bit deeper than it seems at first sight.

I have the following 3 table structures:

user:  id, name
group: id, name
group_members: id, idUser, idGroup

There is a group for super admins that has an id=1.

I need to select all users that are NOT part of this group. However, the tricky part is when they are subscribed to the super admins group and other groups. I tried a LEFT JOIN and JOIN, but they get included in this case. I want to EXCLUDE them from the result if they are subscribed to the super admin group, regardless of the fact if they are subscribed to other groups or not.

Upvotes: 0

Views: 96

Answers (2)

Linger
Linger

Reputation: 15048

SELECT * FROM user
WHERE id NOT IN
(SELECT idUser FROM group_members WHERE idGroup = 1)

Upvotes: 1

AdrianBR
AdrianBR

Reputation: 2588

without a subquery

select idUSER from group_members
group by iduser
having sum(if(idgroup=1,1,0))=0

Upvotes: 1

Related Questions