Skip to content
Advertisement

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

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

Advertisement

Answer

Aggregation offers one approach:

SELECT ChatId
FROM yourTable
WHERE UserId IN ('x', 'y')
GROUP BY ChatId
HAVING MIN(UserId) <> MAX(UserId);
7 People found this is helpful
Advertisement