I have two tables:
chat_room_id (INT - FR)
I want to:
Find a Chat Room, where
User 1 is the creator and
User 2 & 3 are the ONLY participants.
What have I got so far:
SELECT rcc.*, COUNT(*) as participant_count, GROUP_CONCAT(rcp.user_id) as participants FROM `chat_rooms` cr LEFT JOIN chat_participants cp on cp.chat_room_id = cr.id WHERE rcc.user_id = 1 GROUP BY rcc.id HAVING participant_count = 2 AND participants = '2,3' <--- this doesn't look nice
I have two sets of numbers that I want to match for equality. Set
2,3 is my input and I want to match it against
GROUP_CONCAT(rcp.user_id) as participants for equality regardless of their order in their respective sets.
What I’m doing above is a string comparison which obviously isn’t the right way to go.
I would write this as:
SELECT cr.id FROM chat_rooms cr INNER JOIN chat_participants cp ON cp.chat_room_id = cr.id WHERE cr.creator_user_id = 1 GROUP BY cr.id HAVING COUNT(DISTINCT participant_user_id) = 2 AND SUM(participant_user_id NOT IN (2, 3)) = 0;