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
    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.


You don't need self-joining or subqueries at all. This query does the trick:

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.

  • This is not just a query, but a work of art. +1 for a single pass aggregation. Very slick !!! – RolandoMySQLDBA Oct 22 '14 at 9:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.