CODEWITHSUNDEEP
mysqlsqlcounthaving
Dennis Hackethal
Dennis Hackethal

Reputation: 14285

Mysql - find conversation only being held by two users

I have a table called participants with the following fields:

The background is that there are conversations with at least two or more participants. I want to find a conversation that has only been held by two specified users, with no other users in the conversation.

I have come up with the following:

SELECT * 
FROM participants
WHERE user_id = 1 OR user_id = 2
GROUP BY conversation_id
HAVING COUNT(*) = 1

Given this content

participant table image

you can see that the users 1 and 2 share a conversation with user 3 (conversation 1), but also have one conversation alone (conversation 2).

The query above in fact returns the right conversation_id (i.e. 2) - but I am not sure whether the query is correct. When I say HAVING COUNT(*) = 2it returns conversation 1, and I am not sure why. Intuitively I used having count - and it seems to work if set to 1 - but I am not sure what it does in this context.

Is the query correct? If so, why? If not, what do I need to change to make it work?

Upvotes: 2

Views: 451

Answers (2)

Onur Sönmez
Onur Sönmez

Reputation: 1

select conversation_id
from participants
where user_id in (1,2)
group by conversation_id
having count(user_id) = 2

Return two users conversation_id

Upvotes: 0

juergen d
juergen d

Reputation: 204854

Using your query will not work since the where clause filters out the user_ids. Use

SELECT * FROM participants
GROUP BY conversation_id
HAVING sum(user_id not in (1,2)) = 0

user_id not in (1,2) returns 1 if a user_id other than 1,2 are in a conversation and 0 otherwise. So using SUM you can add up all that cases. If none are found then the sum is 0.

Upvotes: 8

Related Questions