SQL: How to check if group of rows already exist in a table

I have a simple chat functionality in my application where signed up users are able to create chat groups with other users. To store the information which user is a member in which chat I am using a simple relationship table:

CREATE TABLE `Chat_Users` (
   `ID_Chat` int(11) NOT NULL,
   `ID_User` int(11) NOT NULL,
 PRIMARY KEY (`ID_Chat`,`ID_User`),
 KEY `ID_User` (`ID_User`),
 CONSTRAINT `Chat_Users_ibfk_1` FOREIGN KEY (`ID_Chat`) REFERENCES `Chats` (`ID`),
 CONSTRAINT `Chat_Users_ibfk_2` FOREIGN KEY (`ID_User`) REFERENCES `Users` (`ID`)
)

Imagine three users (ids: 1, 2, 3) in a group chat (id: 1) and three other friends (ids: 3, 4, 5) in another group chat (id: 2). Then this relationship table would look like:

Chat_ID | Chat_User
-------------------
1       | 1
1       | 2
1       | 3
2       | 3
2       | 4
2       | 5

To avoid multiple chat groups with the exact same users I want to check if an identical one already exists when a users tries to create a new one. I have a string list with the userIDs of that new chat at the time of creation. So I have to check if there is already a chat with the exact same userIDs but unfortunately I do not know a ‘good’ way to do this. My only idea so far was something like

SELECT GROUP_CONCAT(`ID_User`) FROM `Chat_Users`
GROUP BY `ID_Chat`

and then iterate with php over the results and check is one value equals the userIDs list from above. If there are no matches I know that there is no chat so far but this way is of course highly inefficient as soon as the table gets larger.

Any help would be much appreciated.

Answer

A simple method is to use aggregation:

select id_chat
from chat_users cu
group by id_chat
having group_concat(cu.id_user order by cu.id_user) = '1,2,3';

Note that the string needs to contain all three members in order.

You can also express this as:

having sum( cu.id_user in (1, 2, 3) ) = count(*) and
       count(*) = 3  -- number of users

Advertisement

Answer