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