Databases
StackOverflow2013
Postsdbo
POten cheapest room for each city
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POten cheapest room for each city
    text
    copied!<p>I have 3 table Room, Hotel, City, how can i select ten cheapest room and its hotel_id for each city possible to execute the subquery for each city?</p> <pre><code>SELECT price, room_id, hotel_id, city_id FROM Hotel JOIN Room USING(hotel_id) ORDER BY price LIMIT 10 Scheme CREATE TABLE `City` ( `city_id` int(11) NOT NULL AUTO_INCREMENT, `city_name` varchar(50) NOT NULL, PRIMARY KEY (`city_id`) ) CREATE TABLE `Hotel` ( `hotel_id` int(11) NOT NULL AUTO_INCREMENT, `city_id` int(11) NOT NULL, `hotel_name` varchar(100) NOT NULL, PRIMARY KEY (`hotel_id`), CONSTRAINT `Hotel_FK_1` FOREIGN KEY (`city_id`) REFERENCES `City` (`city_id`) ON DELETE CASCADE ON UPDATE CASCADE ) CREATE TABLE `Room` ( `room_id` int(11) NOT NULL AUTO_INCREMENT, `hotel_id` int(11) NOT NULL, `room_name` varchar(255) DEFAULT NULL, `room_price1` int(11) DEFAULT NULL, PRIMARY KEY (`room_id`), CONSTRAINT `Room_FK_1` FOREIGN KEY (`hotel_id`) REFERENCES `Hotel` (`hotel_id`) ON DELETE CASCADE ON UPDATE CASCADE ) </code></pre> <p>query with subquery </p> <pre><code>SELECT r.room_price1, r.room_id, h.hotel_name, c.city_name FROM Hotel h INNER JOIN Room r ON h.hotel_id = r.hotel_id INNER JOIN City c USING(city_id) WHERE ( SELECT count(*) from Room as rt JOIN Hotel ht using(hotel_id) where c.city_id = ht.city_id and rt.room_price1 &lt; r.room_price1 OR (rt.room_price1 = r.room_price1 AND rt.room_id &lt; r.room_id) ) &lt; 3 ORDER BY c.city_name, h.hotel_id, r.room_price1 </code></pre> <p>if some hotels have some room_price1 this query return more then 3 row for city and i need 3 unique hotel per city this query can return 3 room from one hotel</p> <p>ill try use user vars</p> <pre><code>set @num := 0, @type := ''; select * from ( select r.room_price1 pr, r.room_id, h.hotel_name, c.city_name, @num := if(@type = city_id, @num + 1, 1) as row_number, @type := city_id as dummy from Hotel h JOIN Room r USING(hotel_id) INNER JOIN City c USING(city_id) order by city_name, r.room_price1 asc ) as x where x.row_number &lt;=3; </code></pre> <p>this method return wrong sort for row_number &lt; 6 (my init data)</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload