I need to select all rows from user table where row does not have value in join table

I have three tables:

Users, Groups and GroupsUsers

User table:

id: 1 username: admin

id: 2 username: barry

Groups Table:

id: 102 name: Administrator

id: 103 name: Site User

GroupsUser (Join table b开发者_如何转开发etween Users and Groups)

id: 1 user_id: 1 group_id: 102

id: 2 user_id: 2 group_id: 103

id: 3 user_id: 1 group_id: 103

Now the problem I have is: I want to select ALL users from Users table that do NOT belong to 'Administrator' group. What I have attempted is this:

SELECT COUNT(*) AS `count` 
FROM `Users` AS `User` 
LEFT JOIN `GroupsUsers` AS `GroupsUser` 

ON (`GroupsUser`.`user_id` = `User`.`id` AND `GroupsUser`.`group_id` NOT in ( 102 ) ) 

WHERE 
 (
  NOT ( `GroupsUser`.`group_id` IN ( 102 )
 ) 
)

For some reason, this still returns the Administrator account. What I want to do, is return JUST 'barry', or in this case - a COUNT of '1', not '2'.

SELECT COUNT(*) AS `count`
FROM Users AS User
LEFT JOIN GroupsUsers AS GroupsUser
    ON GroupsUser.user_id = User.id AND GroupsUser.group_id = 102
WHERE GroupsUser.user_id IS NULL

The left join attempts to find the user in the GroupUsers table, in the group 102 (Administrators). If the match is found, GroupsUser.user_id is not null, so the reverse condition WHERE GroupsUser.user_id IS NULL keeps only where the match cannot be found, i.e. user is not an administrator.

For the comment "I want to select users that ONLY have that row in the corresponding table", which is neither the same as the question body, nor the title

SELECT COUNT(*) AS `count`
FROM Users AS User
LEFT JOIN GroupsUsers AS GroupsUser1
    ON GroupsUser1.user_id = User.id AND GroupsUser1.group_id = 102
LEFT JOIN GroupsUsers AS GroupsUser2
    ON GroupsUser2.user_id = User.id AND GroupsUser2.group_id <> 102
WHERE GroupsUser2.user_id IS NULL

you are trying to get users that are only "Site User" and at the same time they are not "Adminstrator"

So, if a user belongs to both groups, you want to exclude him from the result...

if that's what you are trying to do, try this:

SELECT `User`.*
FROM `Users` AS `User`
WHERE `User`.id NOT IN (
   SELECT `User2`.id
   FROM `Users` AS `User2`, `GroupsUsers` AS `GroupsUser`
   WHERE
        ( `GroupUser`.`user_id` = `User2`.`id` )
       AND
        ( `GroupUser`.`group_id` = 102 )
)

hope this helps...

good luck with your development...