Select database records which are m:n-related to records from another table

I'm trying to select all records from table A which are related to X certain records in table B (m:n relationship). These are my tables:

hs_hotels: id - name

hs_hotel_options: hotel_id - option_id

hs_options: id - name

Now I have, let's say 3, IDs of certain records from table hs_options and I'm looking for all records from table hs_hotels which are related to the given records from hs_options and also fit some other conditions. At the end, I want a set of records from hs_hotels.

This is my current attempt:

SELECT `hs_hotels`.*
FROM `hs_hotels`
RIGHT JOIN `hs_hotel_options` ON (`hs_hotels`.`id` = `hs_hotel_options`.`hotel_id`)
WHERE `hs_hotel_options`.`option_id` IN (1, 3, 5)
GROUP BY `hs_hotel`.`id`

1, 3 and 5 are IDs of records from table hs_options. This just selects all records from hs_hotels which are related to 1 of the 3 given records from hs_options instead of all 3 records. The problem I have is that I don't know how to solve this. I could try to select all records WHERE option_id = 1 AND option_id = 3 AND... but of course this won't work.

Do you have any ideas how 开发者_JAVA百科to do this? Thanks in advance!

---

Similar to Tom H.'s you need to count the options and then associate it with the hotel.

You can use inner join exists or IN. I chose INNER JOIN

SELECT
    *
FROM
    Hotels H
    INNER JOIN (
      SELECT
         hotel_id
       FROM
        hs_hotel_options
      WHERE 
        option_id IN (1, 3, 5)
      GROUP BY 
         hotel_id
    HAVING COUNT(*) = 3) req_options
   ON h.hotel_id = req_options.hotel_id

---

SELECT
    <column list>
FROM
    Hotels H
WHERE
    (   SELECT COUNT(*)
        FROM
            Hotel_Options HO
        WHERE
            HO.hotel_id = H.hotel_id AND
            HO.option_id IN (1, 3, 5)
    ) = 3

I don't know if this subquery syntax is supported under your RDBMS or not.