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]