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
select conversation_id from chat where participant_id == A AND participant_id == B AND COUNT(participant_id) == 2
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.