Jump to content

Query help - looking for members of two different groups


Go to solution Solved by Barand,

Recommended Posts

Hello,

 

We have a (complicated!) Drupal database in which users can be assigned multiple roles (member, leader, coordinator, etc)

 

I have a query to get info about all our members (users_roles.rid = 7) but I need to get a list of people who are members AND leaders (users_roles.rid = 8 ) but this doesn't work:

SELECT
users.uid,
users.`name`,
users.mail,
users.created,
users_roles.rid,
field_data_field_first_name.field_first_name_value,
field_data_node_venue_region.node_venue_region_tid
FROM
users
INNER JOIN users_roles ON users.uid = users_roles.uid
INNER JOIN field_data_field_first_name ON field_data_field_first_name.entity_id = users.uid
INNER JOIN field_data_node_venue_region ON field_data_node_venue_region.entity_id = users.uid
WHERE
users_roles.rid = 7 and
users_roles.rid = 8

Any advice appreciated!

 

Thanks.

Edited by simonp

Hi gents,

 

Thanks so much for your replies but sadly that doesn't work as it gives me everyone who is a Member of group 7 OR group 8 and I need to know who is in both group 7 AND group 8 (many people have multiple roles).

 

Any ideas? 

 

Cheers

  • Solution

In that case you need to connect twice to the user_role table

SELECT
users.uid,
users.`name`,
users.mail,
users.created,
ur1.rid as rid1,
ur2.rid as rid2,
field_data_field_first_name.field_first_name_value,
field_data_node_venue_region.node_venue_region_tid
FROM
users
INNER JOIN users_roles ur1 ON users.uid = ur1.uid AND ur1.rid = 7
INNER JOIN users_roles ur2 ON users.uid = ur2.uid AND ur2.rid = 8
INNER JOIN field_data_field_first_name ON field_data_field_first_name.entity_id = users.uid
INNER JOIN field_data_node_venue_region ON field_data_node_venue_region.entity_id = users.uid

I repeat:

SELECT
    users.uid
FROM
    users
    JOIN users_roles ON users.uid = users_roles.uid
WHERE
    users_roles.rid IN (7, 

-- make sure that the user is in *both* group 7 and 8 by
-- counting the number of groups:

GROUP BY
    users.uid
HAVING
    COUNT(*) = 2
;

is just another way of writing

 

users_roles.rid = 7 OR users_roles.rid = 8

 

So yes, the point is that the condition should not use "AND"

 

No, the point is to count the rows.

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.