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]
