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: I tried: 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 Answer Aggregation offers one approach:

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

​x
 
Id  ChatId  UserId 1   a       x   <===2   b       z3   a       y   <===4   c       x​

I tried:

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

 
SELECT ChatIdWHERE 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

Aggregation offers one approach:

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

 
SELECT ChatIdFROM yourTableWHERE UserId IN ('x', 'y')GROUP BY ChatIdHAVING MIN(UserId) <> MAX(UserId);​