a nested query question in mysql

Question

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

dcp · Accepted Answer · 2010-01-11 16:58:36Z

3

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

answered Jan 11, 2010 at 16:58

dcp

55.7k24 gold badges149 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Quassnoi · Accepted Answer · 2010-01-11 17:01:51Z

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

answered Jan 11, 2010 at 17:01

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Collectives™ on Stack Overflow

a nested query question in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related