How can i select all users wich have only 1 role. For example i got 4 users:

User 1:

Role 1, Role 2

User 2:

Role 1

User 3:

Role 1, Role 2

User 4:

Role 1

How can i retrieve a list of users wich only got Role 1? But not Role 2.

$query = db_select('users', 'u');
$query->leftJoin('users_roles', 'ur', 'u.uid = ur.uid');
$query->leftJoin('field_data_field_full_name', 'n', 'u.uid = n.entity_id');
$query->fields('u', array('uid','name','picture'));
$query->fields('n', array('field_full_name_value'));
$result = $query->execute()->fetchAll()


u.uid AS uid,
u.name AS name, 
u.picture AS picture,
n.field_full_name_value AS field_full_name_value,
f.field_function_value AS field_function_value,
pi.field_phone_intern__value AS field_phone_intern__value,
ur.rid AS rid,
COUNT(*) AS rolecount
FROM `dev_drupal_wurth-intranet`.users u 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.users_roles ur ON u.uid = ur.uid 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_full_name n ON u.uid = n.entity_id 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_function f ON u.uid = f.entity_id 
LEFT OUTER JOIN `dev_drupal_wurth-intranet`.field_data_field_phone_intern_ pi ON u.uid = pi.entity_id 
GROUP BY u.uid;

This give me the result all the users and their number of roles. Now i tried to filter on this role count with 'WHERE rolecount = 1' but this just gives and sql error.

I'm not very advanced in sql :), still learning.


  • 2
    Either use a sub-query count or group the fields - once you've got the actual SQL query worked out, just edit it into the question and we'll help you convert it to Drupal's DB API if you're struggling.
    – Clive
    Aug 19, 2014 at 13:14
  • I added count and group it by uid. But how can filter on rolecount 1? I keep getting sql error :).
    – mgoubert
    Aug 19, 2014 at 14:05
  • You might review this question for ideas and approaches: drupal.stackexchange.com/questions/11175/…
    – acrosman
    Jun 13, 2016 at 1:09
  • Instead of "where rolecount = 1" use "having rolecount = 1" in the original sql. Is your goal to get all users with exactly one specific role or all users with exactly one arbitrary role?
    – schlicki
    Feb 2, 2017 at 7:05

2 Answers 2


If I were you (and with my poor level), I would proceed in 2 steps:

  1. Get all the users with the role that interests you. Maybe inspire yourself for the query here: Get all users with specific roles using EntityFieldQuery

  2. Browse the query result by loading each user and check if $user->roles has another role than the one you are looking for.

  3. Push the final results in another array.

I don't know if it is a good way to proceed or if there is another way to achieve this, but I think it could do the trick.


You can try this one:

  $sub_query1 = db_select('users_roles', 'ur')
    ->fields('ur', array('uid'))
    ->condition('ur.rid', '3'); // Role 1

  $sub_query2 = db_select('users_roles', 'ur')
    ->fields('ur', array('uid'))
    ->condition('ur.rid', '4'); // Role 2

  $query = db_select('users', 'u');
  $query->leftJoin('users_roles', 'ur', 'u.uid = ur.uid');
  $query->leftJoin('field_data_field_full_name', 'n', 'u.uid = n.entity_id');

  $query->condition('ur.uid', $sub_query1, 'IN');
  $query->condition('ur.uid', $sub_query2, 'NOT IN');

  $query->fields('u', array('uid','name','picture'));
  $query->fields('n', array('field_full_name_value'));
  $result = $query->execute()->fetchAll();

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.