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->condition(...); $query->fields('u', array('uid','name','picture')); $query->fields('n', array('field_full_name_value')); $result = $query->execute()->fetchAll() 

EDIT:

SELECT  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.

Thx