I have a table with
room which has a data of who are in it. Each room has have 0 to any number of people and each person can be in multiple rooms simultaneously. (simplified below)
id name 1 John 1 Peter 1 Jill 2 John <--- room # I want 2 Jill <--- room # I want 3 Someoneelse ...
If I want to look for a room where there is John and Jill and no one else, what would be an elegant & efficient way to select the room number?
Something like this works...
SELECT r1.id FROM `room` r1, `room` r2 WHERE r1.name != r2.name AND r1.id = r2.id AND r1.name = 'John' AND r2.name = 'Jill' AND r1.id IN ( SELECT id FROM user_conversation GROUP BY id HAVING COUNT( * ) = 2 )
But I feel like there would be a much more efficient & elegant way to get this.