Finding one row from pivot table

Question

I have two tables called conversations and users they are bound with the pivot table conversation_user which contains a user_id and a conversation_id column.

| conversation_id | user_id |
|-----------------|---------|
|               1 |       1 |
|               1 |       2 |
|               2 |       1 |
|               2 |       2 |
|               2 |       3 |

I want to send the database some user ids and retrieve the conversation they are in together.

For example:

When I send 1 and 2 i must receive the conversation_id 1, when I send 1, 2 and 3 i must receive the conversation_id 2.

How may I write a such query?

Answer 1

You can use GROUP BY and HAVING:

SELECT conversation_id 
FROM tab                     -- subquery/temp table/whatever
GROUP BY conversation_id 
HAVING SUM(user_id = 1) > 0
   AND SUM(user_id = 2) > 0
   AND SUM(user_id = 3) > 0
   AND SUM(user_id NOT IN (1,2,3)) = 0

SqlFiddleDemo

For 1 and 2:

SELECT conversation_id 
FROM tab
GROUP BY conversation_id 
HAVING SUM(user_id = 1) > 0
   AND SUM(user_id = 2) > 0
   AND SUM(user_id NOT IN (1,2)) = 0