a nested query question in mysql

Question

Hi, I'm trying to learn nested queries in mysql and I'm stuck while selecting all hotels which are 30 miles away from the city and have rooms that cost 150$

I can chose the rooms which are 30 miles away from the city and costs 150 with this query,but can't reach the hotels.

    (select id from Rooms where cost = 150 and id in 
(select r_id from has_rooms where name IN (select name from is_at where l_town in 
(select town from Location where distance_from_city = 30))));



Rooms
+----+------+---------+
| id | cost | type    |
+----+------+---------+
|  1 |  100 | kral    |
|  2 |    0 | kralice |
|  3 |  150 | padisah |
|  4 |  150 | hop     |
|  5 |  150 | boss    |
+----+------+---------+

has_rooms
+------+------+
| r_id | name |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | A    |
|    3 | A    |
+------+------+

is_at
+------+----------+
| name | l_town   |
+------+----------+
| A    | istanbul |
| B    | izmir    |
| C    | kars     |
| D    | adana    |
+------+----------+


select * from Location;
+--------------------+----------+----------------+----------+
| distance_from_city | postcode | street         | town     |
+--------------------+----------+----------------+----------+
|                 30 |     NULL | KENNEDY Street | istanbul |
|                 35 |     NULL | NULL           | kars     |
|                 40 |     NULL | Tenesse        | izmir    |
|                 50 |     NULL | NULL           | adana    |
+--------------------+----------+----------------+----------+

Hotel

+------+--------+
| name | rating |
+------+--------+
| A    |      5 |
| B    |      5 |
| C    |      4 |
| D    |      1 |
+------+--------+

Answer 1

I'm not sure why you want to use nested queries here. Why not do something like this?

SELECT h.name
  FROM rooms     r
     , has_rooms h
     , is_at     i
     , location  l
     , hotel     o
 WHERE r.cost   = 150
   AND h.r_id   = r.id
   AND i.name   = h.name
   AND i.l_town = l.town
   AND l.distance_from_city = 30
   AND i.name = o.name

Answer 2

SELECT  h.*
FROM    hotel h
JOIN    is_at ia
ON      ia.name = h.name
JOIN    location l
ON      l.town = ia.town
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    rooms r
        JOIN    has_rooms hr
        ON      hr.r_id = r.id
        WHERE   hr.name = h.name
                AND r.cost = 150
        )
        AND distance_from_city <= 30