mysql中的嵌套查询问题(a nested query question in mysql)

mysql中的嵌套查询问题(a nested query question in 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 | +------+--------+

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 | +------+--------+

最满意答案

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 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