Max Muster Max Muster - 14 days ago 5
MySQL Question

Get chat_id from the chat which contains specifc users

enter image description here

I would like to determine the chat_id, to which 2 certain users belong. The user_id is known. For example, user 25 and user 28 belong to the chat_id 1. These belong also to the group chat with the chat_id 2. However, the chat_id should be determined which only these two users have. What SQL query can I use? I tried it as follows:

[SELECT chat_id FROM `chat_partners` WHERE private=1 AND (F_user_id=100 OR F_user_id=50) GROUP BY chat_id HAVING count(*)=2;][1]


There, however, I was also given the chat_id 1 when I have two non-existent user_id's specified.

Best regards

Answer

Your attempt does not look far off, but the check for each user needs to happen in the HAVING clause, not the WHERE clause:

SELECT chat_id
FROM chat_partners
WHERE private = 1
GROUP BY chat_id
HAVING SUM(CASE WHEN F_user_id = 25 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN F_user_id = 28 THEN 1 ELSE 0 END) > 0

This will return every chat_id having both users 25 and 28. However, other users may also be present. If you want only chats exclusively containing these two users (e.g. a private chat), then you can add an additional term to your HAVING clause:

HAVING SUM(CASE WHEN F_user_id <> 25 AND F_user_id <> 28 THEN 1 ELSE 0 END) = 0
Comments