CODEWITHSUNDEEP
mysqlsqldatabase
SISYN
SISYN

Reputation: 2269

GROUP BY chat_id - Return only if rows of all criteria are matched

I have a table of chat participants that looks like this:

id         chat_id          participant_id
1          1                5
2          1                7
3          1                9

If the user creates a new chat, we first want to make sure a chat with the requested participants does not already exist, if it does we will just use that chat.

So here let's say I want to create a chat with the participants 5, 7, 9, and 11.

In this case, nothing would be returned and a new chat with a new chat_id would have to be created, since there is no chat that contains all of these members.

However, if we want to create a chat with just participants 5, 7, and 9, the chat_id of 1 should be returned.

Is there an efficient way to structure a query to achieve this without using loops?

I tried something like this but never got desired results:

SELECT chat_id FROM chat_participants WHERE participant IN(5,7,9) GROUP BY chat_id

Any suggestions?

Upvotes: 0

Views: 51

Answers (2)

JohnHC
JohnHC

Reputation: 11205

select chat_id
from
(
select chat_id, group_concat(participant order by participant, ',') as gc
from chat_participants
where chat id in (select chat_id from chat_participants where participant IN(5,7,9))
group by chat_id 
) x1
where x1.gc = '5,7,9'

Upvotes: 2

krishn Patel
krishn Patel

Reputation: 2599

you just need to add condition with count of your requested participants total like this 

SELECT chat_id FROM chat_participants WHERE participant IN(5,7,9) GROUP 
BY chat_id having count(chat_id) = 3

if you pass like IN(5,7,9,11) than your condition will be having count(chat_id) = 4

Upvotes: 2

Related Questions