I have three tables, room table, item table and user table.
item table has column room_id and user_id
table data example is like this:
| room_id | user_id |
|---|---|
| First Room | 1111 |
| First Room | 2222 |
| Second Room | 1111 |
| Second Room | 2222 |
| Second Room | 3333 |
| Second Room | 4444 |
| Third Room | 2222 |
And I want to find room_id where exactly matching member group
Example:
input: [11111, 22222], output: First Room
input: [22222], output: Third Room
input: [11111, 22222, 3333], output: Not Exists
In MySQL, How can I find it?
Thanks.
CodePudding user response：
We can try the following aggregation approach. For users 1111 and 2222:
SELECT room_id
FROM yourTable
GROUP BY room_id
HAVING COUNT(DISTINCT user_id) = 2 AND
SUM(user_id NOT IN ('1111', '2222')) = 0;
The count restriction ensures that there are 2 and only 2 users in the room. The sum restriction makes sure that nobody else besides users 1111 and 2222 are in a matching room.
CodePudding user response：
SELECT room_id
FROM rooms
GROUP BY room_id
HAVING GROUP_CONCAT(DISTINCT user_id) = {sorted CSV list of distinct input values}
For example, for (citate) input: [11111, 22222] it will be
SELECT room_id
FROM rooms
GROUP BY room_id
HAVING GROUP_CONCAT(DISTINCT user_id) = '11111,22222';