SQL Get Users By One Group But Exclude Others

Question

I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D.

Users can belong to multiple groups.

I have the following query but it's not working:

SELECT id
FROM users
LEFT JOIN groups USING (user_id)
WHERE groups.name = 'A'
AND groups.name NOT IN ('B', 'C', 'D')

This just selects all users from group A, how do I exclude anyone who also belongs to groups B, C & D?

Chris Tucker · Answer

SELECT 
    id
FROM 
    users as u
    LEFT JOIN groups 
        ON groups.user_id = u.id
WHERE 
    groups.name = 'A'
    AND NOT EXISTS (
        SELECT 
              1 
        FROM 
            groups 
        WHERE 
            user_id = u.id 
            and name IN ('B', 'C', 'D')
    )

SQL Get Users By One Group But Exclude Others

One Answer

Add your own answers!

Ask a Question