Cum pot obtine room_type si pretul total pentru fiecare camera dintr-o singura interogare din urmatoarele tabele ?
calendar (day)
2018-01-01
2018-01-02
2018-01-03
2018-01-04
pana la 2018-01-20
room_prices (room_id, season_start, season_end, price)
1, 2018-01-01, 2018-01-10, 100
1, 2018-01-11, 2018-01-20, 150
2, 2018-01-01, 2018-01-10, 200
2, 2018-01-11, 2018-01-20, 250
rooms (room_id, room_type)
1, camera simpla
2, camera dubla
Am incercat cu room_prices.room_id=rooms.room_id dar nu merge
Cod: Selectaţi tot
$items = $db->fetch_assoc("SELECT * FROM rooms,
(
SELECT sum(room_price) AS price
FROM calendar
JOIN room_prices on calendar.day >= room_prices.season_start AND calendar.day <= room_prices.season_end
WHERE room_prices.room_id=rooms.room_id AND calendar.day between '2018-05-14' AND '2018-05-17'
) AS totals ORDER BY totals.price DESC");
Vreau sa obtin ceva de genul cu o singura interogare
$items[0]['room_type'] = "camera simpla";
$items[0]['price'] = sum(room_price)
$items[1]['room_type'] = "camera dubla";
$items[1]['price'] = sum(room_price)
Multumesc!