Find rows that has ALL the linked rows

Question

I've got two tables:

User (id, name, etc)
UserRight (user_id, right_id)

I want to find the users who have rights 1, 2 and 3, but no users who only have one or two of these. Also, the number of rights will vary, so searches for (1,2,3) and (1,2,3,4,5,6,7) should work with much the same query.

Essentially:

SELECT *
FROM User
WHERE (
    SELECT right_id 
    FROM tblUserRight 
    WHERE user_id = id 
    ORDER BY user_id ASC
) = (1,2,3)

Is this possible in MySQL?

Answer 1

In correspondance with the errors in my answer pointed out, here a solution with count and a subquery:

SELECT *
FROM User
WHERE 3 = (
    SELECT Count(user_id)
    FROM tblUserRight 
    WHERE right_id IN (1,2,3)
    AND user_id = User.id
)

An optimizer may of course change this to Martin Smith's solution (i.e. by using a group by).

Answer 2

SELECT u.id, u.name ...
FROM User u
JOIN UserRight r on u.id = r.user_id
WHERE right_id IN (1,2,3)
GROUP BY u.id, u.name ...
HAVING COUNT DISTINCT(right_id) = 3

Answer 3

You can also do this using PIVOT, especially if you want a visual representation. I did this on SQL Server - you may be able to translate it.

Declare @User Table (id  Int, name Varchar (10))
Declare @UserRight Table (user_id Int, right_id Int)

Insert Into @User Values (1, 'Adam')
Insert Into @User Values (2, 'Bono')
Insert Into @User Values (3, 'Cher')

Insert Into @UserRight Values (1, 1)
Insert Into @UserRight Values (1, 2)
Insert Into @UserRight Values (1, 3)
--Insert Into @UserRight Values (2, 1)
Insert Into @UserRight Values (2, 2)
Insert Into @UserRight Values (2, 3)
Insert Into @UserRight Values (3, 1)
Insert Into @UserRight Values (3, 2)
--Insert Into @UserRight Values (3, 3)


SELECT *
FROM @User U
    INNER JOIN @UserRight UR
        ON U.id = UR.User_Id
PIVOT
(
    SUM (User_Id)
    FOR Right_Id IN ([1], [2], [3])
) as xx
WHERE 1=1


SELECT *
FROM @User U
    INNER JOIN @UserRight UR
        ON U.id = UR.User_Id
PIVOT
(
    SUM (User_Id)
    FOR Right_Id IN ([1], [2], [3])
) as xx
WHERE 1=1
AND [1] IS NOT NULL
AND [2] IS NOT NULL
AND [3] IS NOT NULL