SQL query to identify a fully contained subset

Question

I scratch 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 is 1 to n CLIENT_ID.

Suppose user A is associated with clients 1,2 and 3. I want to build a query that returns other users who are also associated with all of these clients. They can be associated with a large number of clients, but they must have links to all of the clients of user A.

Example: User B has links to clients 1,2,3,4. User C has links to clients 1.2. Then, the request should return User B, since User B has links to all of the clients of User A. User C should not return, since he has only links to some, but not all, of the clients of User A.

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

Answer 1

Creating multiple prerequisites for name and data type ...

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 do not have a table to verify this, but it should work with a little debugging.

Answer 2

 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} ) 

Unconfirmed and possibly MySQL specific, but something like this should work.