1

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.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
apneadiving
  • 114,565
  • 26
  • 219
  • 213

2 Answers2

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}')
  )
Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • There could be several notifications for the same user, does it break your condition? – apneadiving Nov 19 '13 at 23:49
  • @apneadiving: It does not completely break it, but you might get multiple rows per conversation. Can be amended easily ... – Erwin Brandstetter Nov 19 '13 at 23:51
  • Looks great, cant test now unfortunately. Btw, quick answer for a 93 years old :) – apneadiving Nov 19 '13 at 23:53
  • 1
    Old one can be, but still quick, young padawan, if the force is strong with you. ;) – Erwin Brandstetter Nov 20 '13 at 00:00
  • what book would you recommend for postgresql please? – apneadiving Nov 21 '13 at 12:19
  • @apneadiving: I have read a few, but that's decades ago. Remember, 93! Nowadays, I read the [excellent manual](http://www.postgresql.org/docs/current/interactive/index.html) mostly (strong recommendation), the [Postgres wiki](https://wiki.postgresql.org/wiki/Main_Page), the Postgres mailing lists and selected blog pages like [the one by Depesz](http://www.depesz.com/). And, well, Stackoverflow. – Erwin Brandstetter Nov 21 '13 at 12:49
1
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.

user2989408
  • 3,127
  • 1
  • 17
  • 15