0

I have this query, which fetches all users with the role #7.

$query = 'SELECT DISTINCT(ur.uid) 
  FROM {users_roles} AS ur
  WHERE ur.rid IN (:rids)';

$result = db_query($query, array(':rids' => array(7)));

Some users currently have both roles #7 and #8. I beleive they are all fetched by this query. I would like to exclude users with the role #8.

Can someone help me?

  • this query is working fine for me. – jibran Jun 6 '12 at 22:28
1

Try

$query = 'SELECT DISTINCT(ur.uid) 
  FROM {users_roles} AS ur
  WHERE ur.rid IN (:rids) AND ur.rid NOT IN (:rids2)';

$result = db_query($query, array(':rids' => array(7), ':rids2' => array(8)));

Or if you wanted just the more specific version

$query = 'SELECT DISTINCT(ur.uid) 
  FROM {users_roles} AS ur
  WHERE ur.rid = (:rids) AND ur.rid != (:rids2)';

$result = db_query($query, array(':rids' => 7, ':rids2' => 8));
  • this looks logical to me, however I still get users tagged in both roles :( – davidgmar Jun 7 '12 at 14:09
0

I found it.

$query = 'SELECT DISTINCT(ur.uid) FROM `users_roles` AS ur 
WHERE ur.rid = (:rids) AND ur.uid 
NOT IN (SELECT uid FROM `users_roles` WHERE rid = (:rids2))';

$result = db_query($query, array(':rids' => 7, ':rids2' => 8));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.