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.