simonp Posted January 2, 2016 Share Posted January 2, 2016 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted January 2, 2016 Share Posted January 2, 2016 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 Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted January 2, 2016 Share Posted January 2, 2016 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 ; Quote Link to comment Share on other sites More sharing options...
Barand Posted January 2, 2016 Share Posted January 2, 2016 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" Quote Link to comment Share on other sites More sharing options...
simonp Posted January 2, 2016 Author Share Posted January 2, 2016 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 Quote Link to comment Share on other sites More sharing options...
Solution Barand Posted January 2, 2016 Solution Share Posted January 2, 2016 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 Quote Link to comment Share on other sites More sharing options...
Jacques1 Posted January 2, 2016 Share Posted January 2, 2016 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. Quote Link to comment Share on other sites More sharing options...
simonp Posted January 2, 2016 Author Share Posted January 2, 2016 Thanks so much Barand - that works a treat. Would never have worked it out myself Cheers Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.