Query where there are only two rows with specific data 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    &lt;--- room # I want
2       Jill    &lt;--- 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 &amp; 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 &amp; elegant way to get this.
You don't need self-joining or subqueries at all. This query does the trick:

select
id
from
your_table t
group by id
having sum(name = 'John') + sum(name = 'Jill') = 2 and count(*) = 2;



see it working live in an sqlfiddle


name = 'whatever' in the sum() function returns 1 or 0, true or false.