List:General Discussion« Previous MessageNext Message »
From:Rapthor Date:September 29 2007 3:13pm
Subject:Many-to-many query (chained)
View as plain text  
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.

Thread
Many-to-many query (chained)Rapthor29 Sep
  • Re: Many-to-many query (chained)Peter Brawley29 Sep
    • Re: Many-to-many query (chained)Rapthor29 Sep