List:General Discussion« Previous MessageNext Message »
From:Rapthor Date:September 29 2007 7:55pm
Subject:Re: Many-to-many query (chained)
View as plain text  
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/mysql?unsub=1
> 
> 
> 

-- 
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.

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