Mysql – Query where there are only two rows with specific data

MySQL

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.

Best Answer

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;

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

Related Question