[SOLVED] Is there a way to compare multiple rows in MySQL

Issue

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.

Solution

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.

Answered By – Tim Biegeleisen

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.