SQL query to identify completely contained subset

Question

I am scratching my head to figure out a solution to the following question:

I have a table with two fields, USER_ID and CLIENT_ID. For each USER_ID there are 1 to n CLIENT_IDs.

Let us say that user A is linked to clients 1,2 and 3. I want to construct a query that returns other users that also are linked to all of these clients. They may be linked to more clients, but they have to have links to all the clients of user A.

Example: User B has links to clients 1,2,3,4. User C has links to clients 1,2. The query should then return User B since User B has links to all of User A's clients. User C should not be returned, since he only has links to some but not all of the clients of User A.

This seems like a deceptively simple problem, but I can't for the life of me come up with a query that satisfies my constraints. Are the any experienced SQL gurus that can help me?

Answer 1

SELECT uc.user_id, u.username, COUNT(*) as client_count
FROM user u
INNER JOIN user_client uc
USING (user_id)
WHERE uc.client_id IN (
  SELECT client_id
  FROM   user_client
  WHERE  user_id = {ID of user A}
)
GROUP BY uc.user_id, u.username
HAVING client_count = (
  SELECT COUNT(*)
  FROM   user_client
  WHERE  user_id = {ID of user A}
)

Untested and possibly MySQL specific but something like this should work.

Answer 2

Making a number of name and datatype assumptions...

DECLARE
  @UserId  int
 ,@ClientCount  int

DECLARE @Clients as table
 (ClientId  int  not null)

--  All clients for the "target" user
INSERT @Clients
 select Clientid
 from MyTable
 where UserId = @userId

--  Track how many there are
SET @ClientCount = @@rowcount

--  List all users that have those clients
SELECT mt.UserId, count(*) HowMany
 from Mytable mt
  inner join @Clients cl
   on cl.ClientId = mt.Clientid
 where UserId <> @UserId
 group by mt.UserId
 having count(*) = @ClientCount

I don't have a table to test this against, but it should work with little debugging.