Keren Keren - 14 days ago 7
MySQL Question

How to select conversations where only two participants involved?

I need to check if participant A and participant B ever had any conversation between them privately. If they two involved in group chat, I don't want that conversation.

So how do I structure my query where it will check if

count(participant_id) == 2
?

I tried this but it gives error:

select conversation_id from chat where participant_id == A AND participant_id == B AND COUNT(participant_id) == 2


table structure
enter image description here

Answer
SELECT
      conversation_id
FROM chat
GROUP BY
      conversation_id
HAVING COUNT(DISTINCT participant_id) = 2
AND MAX(participant_id) IN (A,B)
AND MIN(participant_id) IN (A,B)

The HAVING clause is required when trying to evaluate values that are calculated by aggregate functions such as count. You need to count just the unique ID's involved in the conversation too.

By the way, if this query returns nothing that may mean you don't have any conversations that meet the profile.