Is there a way to compare multiple rows in MySQL

Question

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.

Answer 1

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.

Answer 2

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';