CODEWITHSUNDEEP
mysqlsqlaggregate-functionshaving-clause
ApplePie
ApplePie

Reputation: 1185

How to check if two users have a common chat room?

Currently, I am trying to figure out how to find the common chat rooms between two users by checking their email addresses.

chat_rooms table:

id           email                chat_room_id  
----------------------------------------------
1          [email protected]        3
2          [email protected]        3
3          [email protected]           42
4          [email protected]          64
5          [email protected]        7
6          [email protected]           19
7          [email protected]        6

How can I write a MySQL query that can return the common chat_room_id of [email protected] and [email protected]. Please advise.

Upvotes: 0

Views: 224

Answers (1)

GMB
GMB

Reputation: 222582

You can do this with aggregation:

select chat_room_id
from mytable
where email in ('[email protected]', '[email protected]')
group by chat_room_id
having count(*) = 2

This assumes that the same user cannot appear twice in the same room. If that's not the case, then you would just change the having clause to:

having count(distinct email) = 2

Or:

having min(email) <> max(email)

Upvotes: 2

Related Questions