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?

score 0 · Accepted Answer · answered Jul 16 '12 at 05:30

0

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
        )

answered Jul 16 '12 at 05:30

Andomar

232,371
49
380
404

Thanks! This worked like a charm! I wasn't entirely clear when I said 4 & 5, I meant or and this works great! – MD3 Jul 16 '12 at 10:34

score 0 · Answer 2 · answered Jul 16 '12 at 06:04

0

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

answered Jul 16 '12 at 06:04

Madhivanan

13,470
1
24
29

I wasn't clear enough in my question. I meant to say 4 or 5 but this does work as advertised! In fact, the more I play with it the more I realize how much fine grained control you can get with it. Thanks! – MD3 Jul 16 '12 at 10:33

score 0 · Answer 3 · answered Jul 16 '12 at 09:56

0

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

answered Jul 16 '12 at 09:56

Sébastien - Vélhost

644
4
8

A quick test shows that this seems to work. I'll really have to dig down later tomorrow to figure out if it returns different results from the first answer. Is this a short hand notation for NOT EXISTS? – MD3 Jul 16 '12 at 10:36
It's not a short hand for NOT EXISTS. This solution avoids going through a subquery. Can you test the speed between the two solutions? I think my solution is more faster. – Sébastien - Vélhost Jul 16 '12 at 11:37
Right now, I have such a small dataset (~50 records) that the two solutions are neck in neck every single time I run a query. When I have a larger dataset (or the time to create a fake one) I'll get back to you on that. – MD3 Jul 16 '12 at 16:48

DISTINCT or GROUP BY used with NOT IN returns wrong results

3 Answers3