CODEWITHSUNDEEP
mysqldatabase
Mark Cameron
Mark Cameron

Reputation: 2369

MySQL, Select field depending on multiple other rows in table

I'm implementing a messaging system on my site, and have a table to store the conversation participants like so:

conversation_id     user_id
3                   2   
3                   28  
4                   1   
4                   2   
5                   1   
5                   2 
5                   28

I can't find a query that will let me check if a conversation already exists between 2 users (or more). I basically want 3 (conversation_id) to be returned if user 2 is sending to user 28, or vice-versa, like that I can keep the conversation going even if they haven't explicitly replied to a previous message.

Upvotes: 0

Views: 46

Answers (1)

juergen d
juergen d

Reputation: 204756

With the having clause you can filter to only the conversation_ids where both users participate

select conversation_id
from your_table
where user_id in (2, 28)
group by conversation_id
having count(distinct user_id) = 2

Upvotes: 3

Related Questions