I am trying to check if multiple records exists with pageId IN(?,?,?) in the chatParticipants table. This is an example of the table structure: This is the SQL query I have tried: Expected behavior: If a conversation already exists, with the given (x) participants, that is, with the same chatId value, then it must return the chatId value. The above

Check if multiple records exist, with the same id

I am trying to check if multiple records exists with pageId IN(?,?,?) in the chatParticipants table.

This is an example of the table structure:

This is the SQL query I have tried:

SELECT chatId FROM chatParticipants GROUP BY chatId
HAVING SUM(pageId NOT IN('deb47fba9bc6936ed76e831578baa480','123')) = 0

Expected behavior:

If a conversation already exists, with the given (x) participants, that is, with the same chatId value, then it must return the chatId value.

The above SQL does work, with two inputs. However, the below query still returns the chatId, which it should not. (As it has the ‘144’ which is not a participant)

SELECT chatId FROM chatParticipants GROUP BY chatId
HAVING SUM(pageId NOT IN('deb47fba9bc6936ed76e831578baa480','144','123')) = 0

Any ideas why it does this? I only want to get an output, if all the participants match in the query, and has the same chatId.

Edit 2:

This SQL will return two chat IDs.

SELECT chatId FROM chatParticipants WHERE pageId 
IN ('123','deb47fba9bc6936ed76e831578baa480') GROUP BY chatId HAVING COUNT(*) = 2

Output:

016030f5ba6d47c31f7f22d92507faf1
43bcd7340f1abf7eda09fcccffdbfe60

When in fact, the expected output should only return the chat id for 43bcd7340f1abf7eda09fcccffdbfe60

Any ideas?

Answer

Could this solve your problem?

SELECT chatId FROM chatParticipants WHERE chatId IN (
  SELECT chatId FROM chatParticipants WHERE pageId
  IN ('123','deb47fba9bc6936ed76e831578baa480') GROUP BY chatId HAVING COUNT(*) = 2
) GROUP BY chatId HAVING COUNT(*) = 2

Hope this helps.

Advertisement

Answer