2018-07-16 12:49 阅读 23


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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    dongzhi1950 dongzhi1950 2018-07-16 13:04

    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

    点赞 评论 复制链接分享