i want the query to fetch the chat-id between 2 users I already have both of there user-id but don't know how to do that. there are a lot of chat-id's stored with every user-id but i only need to fetch 2 records which have the same chat-id and 2 different user-id which i will be providing the query.Thanks for reading and helping....

+-------+-------+
|chat-id|user-id|
+-------+-------+
|1      |1      |
+-------+-------+
|1      |2      |
+-------+-------+
|2      |1      |
+-------+-------+
|2      |3      |
+-------+-------+
|3      |2      |
+-------+-------+
|3      |3      |
+-------+-------+
|4      |1      |
+-------+-------+
|4      |4      |
+-------+-------+

1 Answers

2
Gordon Linoff On Best Solutions

If you want chats between two particular users:

select chat_id
from t
where user_id in (@id1, @id2)
group by chat_id
having count(*) = 2;

This assumes that the table does not have duplicates. If it can, then use count(distinct user_id) = 2.