Skip to content
Advertisement

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:

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:

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

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.

Advertisement

Answer

A simple method is to use aggregation:

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

You can also express this as:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement