From: Peter Brawley
Date: September 29 2007 3:55pm
Subject: Re: Many-to-many query (chained)
List-Archive: http://lists.mysql.com/mysql/209311
Message-Id: <46FE755F.7050707@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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