SQL to select the value of a column matching multiple values in another column?-CodePudding

I have a Chat_User join table, and want to retrieve the ChatId of a between specified UserId of x and y.

Chat_User table:

Id  ChatId  UserId 
1   a       x   <===
2   b       z
3   a       y   <===
4   c       x

I tried:

SELECT ChatId
WHERE UserId IN ('x','y')
GROUP BY ChatId;

But it returns ChatId of a and c, but I only want a, as it has BOTH of my specified UserId(x and y).

Any way to achieve this?

Thanks

CodePudding user response：

Aggregation offers one approach:

SELECT ChatId
FROM yourTable
WHERE UserId IN ('x', 'y')
GROUP BY ChatId
HAVING MIN(UserId) <> MAX(UserId);

CodePudding user response：

Another approach is starting only with one that you KNOW you want, and re-self-join to same table by that ID and the other user. Make sure that there is an index on the table on (userid, chatid ) to help optimize the query.

select distinct
      YT.ChatID
   from
      YourTable YT
         JOIN YourTable YT2
            on YT2.UserID = 'y'
            AND YT.ChatID = YT2.ChatID
   where
      YT.userID = 'x'

This way, the outer WHERE clause is only ever considering those chats at a MINIMUM of one of the parties, so you are not even considering anyone else. Only after those that have 'x' will it consider looking at the second instance for the 'y' user having the same chat.