Joined elements should *all* match some criteria

Question

I have a setup like:

conversations(id) 

notifications(id, conversation_id, user_id)

users(id)

If a conversation concerns someone, the user is linked to the conversation through one notification.

GOAL: I'm looking for conversations which concern exclusively users with given ids.

---

The obvious:

SELECT DISTINCT conversations.* 
FROM "conversations" 
INNER JOIN "notifications" ON "notifications"."conversation_id" = "conversations"."id"
WHERE notifications.user_id IN (1, 2)

doesn't work because it would also retrieve a conversation concerning:

• users with id 1, 2 and 3
• users with id 1 and 4
• users with id 2 and 4

and this is not desired.

Answer 1

This is assuming that each user can only be joined once to a conversation:

SELECT c.*
FROM   conversations c
JOIN   notifications n1 ON n1.conversation_id = c.id
JOIN   notifications n2 ON n2.conversation_id = c.id
WHERE  n1.user_id = 1
AND    n2.user_id = 2
AND    NOT EXISTS (
   SELECT 1
   FROM   notifications n3
   WHERE  n3.conversation_id = c.id
   AND    n3.user_id <> ALL ('{1,2}')
  )

This is a special case of relational division. We have assembled a whole arsenal of techniques under this this related question:
How to filter SQL results in a has-many-through relation

The special requirement is to exclude additional matches. I use NOT EXISTS for this end. You could also use LEFT JOIN / IS NULL or NOT IN. More details here:
Select rows which are not present in other table

There could be several notifications for the same user

To avoid duplicate conversations in the result you can use DISTINCT or GROUP BY in addition to the above. Or you suppress duplicates to begin with (probably faster):

SELECT c.*
FROM   conversations c
WHERE  EXISTS (
   SELECT 1
   FROM   notifications n1
   JOIN   notifications n2 USING (conversation_id)
   WHERE  n1.conversation_id = c.id
   AND    n1.user_id = 1
   AND    n2.user_id = 2
  )
AND    NOT EXISTS (
   SELECT 1
   FROM   notifications n3
   WHERE  n3.conversation_id = c.id
   AND    n3.user_id <> ALL ('{1,2}')
  )

Answer 2

SELECT DISTINCT conversations.* 
FROM conversations c
    JOIN notifications n1 ON n1.conversation_id = c.id
    JOIN notifications n2 ON n2.conversation_id = n1.conversation_id AND n1.id <> n2.id
WHERE n1.user_id = 1 AND n2.User_Id =2 

I think the question asks for conversations between User_Id = 1 and User_Id = 2... If nobody other than 1 and 2 should be part of the conversation, then Eric's answer is what you need.