I have three tables,
item table and
item table has column
table data example is like this:
And I want to find room_id where exactly matching member group
[11111, 22222], output:
[11111, 22222, 3333], output:
In MySQL, How can I find it?
We can try the following aggregation approach. For users
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
2222 are in a matching room.
Answered By – Tim Biegeleisen
Answer Checked By – Mary Flores (BugsFixing Volunteer)