MySQL Merging my selects together

Question

and thanks for taking the time to try and help me.

Informations

I'm currently using CodeIgniter if it might have anything to do with your answer ;).

Problem

I'm in a hotel site, trying to figure out how to do my reservation rooms.

I want users to select a list of available services and return to them, a list of rooms that contains these services ( all of them ) AND after that, a list that contains at least one. This way I'll show to them a list of rooms that comply with all their need, and one that might do the trick, but doesnt have everything.

Here's how I store my services for my rooms ( Here might lie my problem in fact ... )

Table "services_rooms"
id_services_rooms | id_room | id_service
1                 | 1       | 1
2                 | 1       | 2
3                 | 1       | 3
5                 | 1       | 5
11                | 2       | 2
12                | 2       | 3
...               | ...     | ...

How can I manage to do my SQL to ask my server give me all of the rooms that contains the services 1, 2 AND 3, therefore, only my "id_room" 1 should come back ?

I've tried doing some joins / group_bys but the most I got was for exemple, 3 row coming back saying :

Return rows :
ID_ROOM 1 | ID_SERVICE 1
ID_ROOM 1 | ID_SERVICE 2
ID_ROOM 1 | ID_SERVICE 3

Another way to see it, would be like that : I want to ask my server which rooms contains ALL of these services : 1,2,3

It would answer : ID_ROOM 1.

I've seen a couple of other questions talking about merges and such but couldn't quite apply their answers to my problem.

Thanks again.

Answer 1

This is called Relational Division.

SELECT id_room
FROM services_rooms
WHERE id_service IN (1,2,3)
GROUP BY id_room
HAVING COUNT(*) = 3

• SQLFiddle Demo

if unique constraint was not enforced on id_service for ech id_room, DISTINCT is required.

SELECT id_room
FROM services_rooms
WHERE id_service IN (1,2,3)
GROUP BY id_room
HAVING COUNT(DISTINCT id_service) = 3