mysql只拉取用户没有的行

Question

My user group system has a table with these fields: user_id - group_id

So a user can have multiple groups, one for each row.

What I want to do, is pull records where they have a group_id of say "6" but not where they also have a group_id row where the value is "9".

I tried this:

SELECT u.`username` FROM `users` u INNER JOIN `user_group_membership` g ON u.user_id = g.user_id WHERE g.group_id != 9 AND g.group_id = 6 ORDER BY u.`username` ASC

However, that doesn't work, it still pulls in users who have a group_id that = 9.

Answer 1

As I understand you need to retrieve users in group 6 but not in group 9

so you can try this solution

SELECT u.`username` 
FROM `users` u 
INNER JOIN `user_group_membership` g6 ON u.user_id = g6.user_id and g6.group_id = 6 
LEFT  JOIN `user_group_membership` g9 ON u.user_id = g9.user_id and g9.group_id = 9 
WHERE g9.group_id is null
ORDER BY u.`username` ASC

you have issue in your sql because this condition will return all users in group 6 regardless if he is in group 9 or no

g.group_id != 9 AND g.group_id = 6

you need to edit your question to be more clear