mysql中的一个嵌套查询问题

Question

我正在尝试学习 mysql 中的嵌套查询，但在选择所有酒店时陷入困境 距离城市 30 英里，房间价格 150 美元

我可以通过此查询选择距离城市 30 英里、价格 150 美元的房间，但无法到达酒店。

    (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

我不确定你为什么要在这里使用嵌套查询。为什么不做这样的事情呢？

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