CODEWITHSUNDEEP
phpmysql
112233
112233

Reputation: 2466

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

Upvotes: 0

Views: 65

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

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.

Upvotes: 2

Related Questions