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.

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

Search chat messages - Android - Google Chat Help, You might temporarily see references to both names until this change rolls out everywhere. You can search all of your messages in Google Chat. Or, you can look for messages with specific people or certain types of content, such as images. When you need to communicate and collaborate with people outside your organization, Microsoft Teams gives you two different ways to make that happen. The first – external access (federation) – lets you find, call, and chat with users in other domains (for example, contoso.com). The second

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);

If I delete a whole conversation on FB Messenger, will the other , Yes, the other person will still see the conversation. Why? When you send a message to someone on messenger, you have the ability to delete for both of you � If you’re part of a conversation on classic Hangouts, you can find the contact info of people in the chat. Notes: If you use Hangouts with a work or school account, you might not be able to find contact info. Starting in April, 2019, profiles and names in Google+ might not show in Hangouts.

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

Telegram adds the option to delete chats on other people's devices , Telegram adds the option to delete chats on other people's devices could delete their own sent messages, both on their device and for others. entire chat history clean on your device and on anyone else's who was part of the chat. Get the tough-to-find Nintendo Switch Pro controller for $10 off Plus,� The websites or your location are blocked using IP Address here in my this trick, I am telling you how to track a person’s location i.e., IP Address by doing a simple chat with him/her on Facebook, Google+ or any other social network.

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

Telegram lets users delete messages from both sides of a chat , Telegram lets users delete messages from both sides of a chat within a private chat from the sender's side as well as the recipient's side. to delete will " disappear for both you and the other person -- without leaving a trace. How does one get proof of being scammed, if its evidence has been wiped out. However, it is equally possible to connect with immature people who use fake information to cultivate false relationships. Sorting out how and why people are communicating online is part of the learning curve of modern life. It is possible to find quality, mature people to chat with online.

Six things to know about chat in Microsoft Teams, From individual chats with just one other person to group chats and conversations You'll see their name and any group chats they're a part of with you listed. No one will get any notification that chat has been deleted. No one will get any message or something similar that chat has been deleted. If you clean a chat, all conversations will be cleaned but chat window will still remain. Other person won’t get any notification or a message when you clean a chat.

Telegram adds 'delete everywhere' nuclear option to private chats , The “delete any message in both ends in any private chat, anytime” where he writes: “We know some people may get concerned about the� start the chat via S4B. The chat is possible only when it is started from the other side. Otherwise - when we start the chat in S4B, our messages are redirected to the recipients email, no matter the fact that the person is online. The test : Downloaded desktop app, log in, started a new chat to external contact -message appeared to be sent.

Everything you need to know about Google Chat, You'll see any chat rooms you've been invited to. Or, to call just one person, open a direct message with them and click If you've ever used Google Talk and Slack, you'll find both experiences combined in some ways in Google Chat. Published in 2018, this piece was updated by Justin Pot to include� The Zoom video meeting and chat app has become the wildly popular host to millions of people working and tricks and hidden features you can find to upgrade your Both free and paid Zoom

Comments
  • @Thorston Just wanted to know is there any way like sum(person_id)=22 and count(*)=2 in having to compensate person_id
  • @Himanshu Ahuja: Yes. Here are two options: (1) having sum(when person_id in (2,22)) then 1 else 0 end) = 2. (2) where person_id in (2,22) group by chat_id having count(*) = 2.
  • Perfect but second where statement should not work in all cases as the Op's query says 20,2 can be there with other values as well Thanks mate :)
  • @imanshu Ahuja: The second statement works very well. We use WHERE to only get rows with persons 2 and 20. Then we see whether we get them both (count(*) = 2). Only if there could be duplicates in the table (which wouldn't make sense), we'd have to count(distinct person_id) instead.
  • Yeah it could have been an error if the groups were to not fall in place as i guess groups are falling in place meaning 1 group 1 count, 2nd group 2nd count
  • Now it looks fine.