By ponkarthik on

I have a site where users have multiple roles. Some users have two roles(both x,y) but some only role x. Can someone kindly point me as to how I can write a mysql query to select users with role x only but not both role x and y ?

Thanks

⋅ Categories: Drupal 6.x

Comments

narayanis’s picture

narayanis commented

SELECT DISTINCT uid
FROM users_roles
WHERE rid = [good role]
AND uid NOT IN
(SELECT uid
FROM users_roles
WHERE rid = [bad role])

ponkarthik’s picture

ponkarthik commented

Many thanks for the quick reply. I just arrived at a solution using left join

SELECT a.uid FROM (SELECT uid,rid FROM users_roles WHERE rid = [good role]) a LEFT JOIN (SELECT uid, rid FROM users_roles WHERE rid = [bad role]) b ON a.uid=b.uid WHERE b.rid IS NULL

But your seems to be much simpler and elegant!

Amazed at the speed with which you replied.