Mysql – SQL Get Users By One Group But Exclude Others


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:

FROM users
LEFT JOIN groups USING (user_id)
AND 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?

Best Answer

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