CODEWITHSUNDEEP
mysqlsql
Vincent Duprez
Vincent Duprez

Reputation: 3892

Mysql find rows equal to some values but limited by count

I have a conversation app where users are assigned to conversations. One conversation can have 1, 2 or more participants, I use a table to link users to conversations :

enter image description here

When user 5 wants to initiate a new conversation with user 6, I don't want to create a new conversation because user 5 and 6 already have an ongoing conversation_id.

They actually also have a different group conversation with user 10

I have 2 parameters : it's user 5 that wants to speak to user 6 How can I query this table to find if a conversation exists only between these 2 users?

I tried this : 

SELECT *,count(id) FROM `conversation_members`
WHERE user_id IN (5,6)
GROUP BY `conversation_id`

enter image description here

But

In the end I just want to retrieve ID 1 as this is the conversation including user 5 and 6 and nobody else. Is this possible only with a Mysql Query?

Upvotes: 1

Views: 31

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Use the sum of a case expression along with the count in the having clause:

SELECT conversation_id
FROM conversation_members
GROUP BY conversation_id
HAVING SUM(case when user_id IN (5, 6) then 1 end) = 2 
AND COUNT(*) = 2;

The ability to use this HAVING SUM(user_id IN (5, 6)) = 2 is not common and is a "MySQL curiosity" I believe. So I suggest you use a case expression instead which I think is also easier to read/understand.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

You can do:

SELECT conversation_id
FROM conversation_members
GROUP BY conversation_id
HAVING SUM(user_id IN (5, 6)) = 2 AND
       COUNT(*) = 2;

This will return all conversations (if any) that have exactly those members.

Upvotes: 2

Related Questions