Query help - looking for members of two different groups

[simonp]

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 January 2, 2016 by simonp

[Barand]

A user record cannot have role of 7 and 8 at the same time. You need to search for for those whose role is 7 or 8

WHERE
users_roles.rid = 7 OR
users_roles.rid = 8

[Jacques1]

I think the OP wants all users who belong to both the group 7 and the group 8:

SELECT
    users.uid
FROM
    users
    JOIN users_roles ON users.uid = users_roles.uid
WHERE
    users_roles.rid IN (7, 
GROUP BY
    users.uid
HAVING
    COUNT(*) = 2
;

[Barand]

users_roles.rid IN (7, 8)

 

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"

[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

[Barand]

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

[Jacques1]

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.

[simonp]

Thanks so much Barand - that works a treat. Would never have worked it out myself

 

Cheers