mysql中如何嵌套查询_MySQL中的嵌套查询问题

我正在尝试学习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 |

+------+--------+