DISTINCT or GROUP BY used with NOT IN returns wrong results

Question

I have two tables
users with columns: uid, name, mail, etc
users_roles with columns: uid, rid

In users_roles, each time a role is added to a user, it is listed in the users_roles table. So, let's say user 1 has roles 1 and 4. In the table:

users_roles:  
uid | rid  
 1  |  1  
 1  |  4  

I need to return all users who don't have roles 4 OR 5. I have tried using both Group By and Distinct combined with NOT IN. The problem I keep running into is if a user has both roles 1 and 4, they will be returned in the results. Below is a an example of my Group By query:

SELECT *
FROM users AS u
LEFT JOIN users_roles AS ur ON u.uid = ur.uid
WHERE ur.rid NOT
IN ( 4, 5 )
GROUP BY ur.uid

I have tried sub-queries as well to no avail because the issue seems to be that Group By combines rows after finishing the query. So, it simply finds the record containing uid 1 rid 4 and returns it in the results.

The Drupal module Views that I can't use (due to security issues with Views Bulk Operations) accomplishes the desired results by doing the following:

LEFT JOIN users_roles ON users.uid = users_roles.uid 
AND (users_roles.rid = '4' OR users_roles.rid = '5')

For long term maintenance I don't want to have to update the code every single time we add a role and this is going to make for one long query.

I looked at the following:
Aggregating Rows
Filtering distinct rows in SQL

While there are Drupal functions that will let me get the list of role ids where I could unset the roles I don't want show up in the resulting array, I feel like I am missing a fundamental understanding of SQL. Is there a better way to do this in SQL?

Answer 1

I need to return all users who don't have roles 4 & 5

select  *
from    users u
where   not exists
        (
        select  *
        from    users_roles ur
        where   ur.rid in (4,5)
                and ur.uid = u.uid
        )

Answer 2

If you want to check for no existance of both 4 and 5 (and not neccessarily one of them), you can use

select  * 
from    users u 
where   not exists 
        ( 
        select  uid
        from    users_roles ur 
        where   ur.rid in (4,5)     
                and ur.uid = u.uid 
    group by uid having count(distinct rid)=2
        ) 

If the list is long you can use a mapping table with all possible values and use that in the above query

Answer 3

I would like that :

SELECT *
FROM users AS u
LEFT JOIN users_roles AS ur ON (u.uid = ur.uid AND ur.rid IN ( 4, 5 ) )
WHERE ur.rid IS NULL
GROUP BY u.uid