CODEWITHSUNDEEP
sqlpostgresql
atkayla
atkayla

Reputation: 8859

Find the chat both persons are a part of

chat_person
---
id chat_id person_id
1   1      20
2   1      19
3   2      19
4   2      3
5   3      19
6   3      2

I am trying to find the chat_id where p1=20 and p2=2 are both in. If there are none, return none.

SELECT DISTINCT "t1".chat_id
FROM "chat_person" t1
WHERE 
    EXISTS (
        SELECT 1 FROM "chat_person" t2
        WHERE "t2".person_id = 20
    )
    AND "t1".person_id = 2

This query is incorrectly returning chat_id: 3. There is no common chat_id with both person_id=20 and person_id=2, so it should not return anything.

Upvotes: 0

Views: 51

Answers (4)

Himanshu
Himanshu

Reputation: 3970

Is this what you want? 

SELECT chat_id, count(distinct 
person_id) from table 
group by chat_id having 
  count(case when person_id in (2,20)
 then person_id end)=2

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

you could try like below if you need all the others fields

select t1.* from chat_person t1
 where exists ( select 1 from chat_person t2 where t2.chat_id=t1.chat_id
                                and person_id in (2,20)
                               having count(distinct person_id)=2)

or you can simply do like below if you just need chat_id

   select chat_id from cte t2 where 
   person_id in (2,20)
   group by chat_id
   having count(distinct person_id)=2

demo

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95053

The most simple way is aggregation:

select chat_id
from chat_person
group by chat_id
having bool_or(person_id = 2) and bool_or(person_id = 20);

Upvotes: 3

Avi
Avi

Reputation: 1845

I think you may have missed to add where condition in exist. 

 SELECT DISTINCT "t1".chat_id
FROM "chat_person" t1
WHERE 
    EXISTS (
        SELECT 1 FROM "chat_person" t2
        WHERE "t2".person_id = 20 and t2.ChatID = "t1".chat_id  
    )
    AND "t1".person_id = 2

Upvotes: 3

Related Questions