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.
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