Many-to-many query (chained)
Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- View this message in context: http://www.nabble.com/Many-to-many-query-%28chained%29-tf4539759.html#a12956571 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many-to-many query (chained)
Rapthor, Try ... SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2) JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2; BTW you can't name a table 'group'; it's a reserved word. PB - Rapthor wrote: Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Many-to-many query (chained)
Great! With a little adaption it works! Yes and of course I have to use another name for groups :) It was just an example. I have slightly different table settings. SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (ur.role_id=1 OR ur.role_id=2) JOIN user_group ug ON u.id=ug.user_id AND (ug.group_id=1 OR ug.group_id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2 GROUP BY name; Thanks! You really saved my day! pzbrawl wrote: Rapthor, Try ... SELECT u.name FROM user u JOIN user_role ur ON u.id=ur.user_id AND (u.id=1 OR u.id=2) JOIN user_group ug ON u.id=ug.user_id AND (u.id=1 OR u.id=2) HAVING COUNT( DISTINCT ur.role_id ) = 2 AND COUNT( DISTINCT ug.group_id ) = 2; BTW you can't name a table 'group'; it's a reserved word. PB - Rapthor wrote: Hi SQLers, I am searching for an SQL command to combine several many-to-many queries. At the end I only want to get results suitable for all restrictions. Better show you my tables: USER id name 1 frank ROLE id name 1 admin 2 general GROUP id name 1 groupA 2 groupB USER_ROLE user_id role_id 1 1 1 2 USER_GROUP user_id group_id 1 1 1 2 The query I am searching for should only process USERs that have ROLE 1 and 2 AND furthermore have GROUPs 1 and 2 at the same time. How to chain these restrictions into ONE SQL-query? What would the query look like? The following query would only get me all USERs having ROLE 1: SELECT * FROM USER WHERE id IN (SELECT DISTINCT USER_ROLE.user_id FROM USER_ROLE WHERE USER_ROLE.role_id = 1); Thanks in advance! I really need help with this! Thanks again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Many-to-many-query-%28chained%29-tf4539759.html#a12959127 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
