Q:

SQL query to retrieve conversations records

I have 2 SQL tables: Conversations and Users. Conversations has many Users and Users has many Conversations. So I have the join table Conversations_users that has 2 fields: conversation_id and user_id.
Now I know the ids of some users and I want to retrieve the conversations where all these users are in the same conversation. Someone can help me?

A:

One way is to filter the Conversations_users table by user, group by conversation and then filter the resulting groups for those that contain the requisite number of (distinct) users:
SELECT   Conversations.*
FROM     Conversations JOIN Conversations_users USING (conversation_id)
WHERE    Conversations_users.user_id IN (1,2,3)
GROUP BY conversation_id
HAVING   COUNT(DISTINCT Conversations_users.user_id) = 3

If (conversation_id, user_id) is guaranteed to be unique in Conversation_users (e.g. it is that table's PRIMARY KEY), one can use COUNT(*) instead of the (expensive) COUNT(DISTINCT Conversations_users.user_id).

