Reputation: 1032
This is my query for available rooms in choosen period:
SELECT rooms.room_id
FROM rooms
WHERE rooms.room_id NOT IN (
SELECT reservations.room_id
FROM reservations
WHERE ( reservations.arrivaldate >= $arrival_datetime
AND reservations.departuredate <= $departure_datetime
)
OR ( reservations.arrivaldate <= $arrival_datetime
AND reservations.departuredate >= $arrival_datetime
)
OR ( reservations.arrivaldate <= $departure_datetime
AND reservations.departuredate >= $departure_datetime
)
);
How to add average room price column for selected period(from $arrival_datetime to $departure_datetime) from another table (room_prices_table), for every room_id returned from above query. So I need to look in columns whos name is same as room_id...
room_prices_table:
date
room0001
room0002
room0003
...
Something like
SELECT AVG(room0003)
FROM room_prices_table
WHERE datum IS BETWEEN $arrival_datetime
AND $departure_datetime
??
Upvotes: 1
Views: 1492
Reputation: 169534
Something like this:
SELECT
r.room_id,
COALESCE(AVG(rpt.room0003), 0) AS AVERAGE_RATE
FROM rooms r
LEFT OUTER JOIN room_prices_table rpt
ON r.room_id = rpt.room_id
AND
rpt.datum BETWEEN $arrival_datetime AND $departure_datetime
WHERE
r.room_id NOT IN (
SELECT reservations.room_id
FROM reservations
WHERE (reservations.arrivaldate >= $arrival_datetime AND
reservations.departuredate <= $departure_datetime) OR
(reservations.arrivaldate <= $arrival_datetime AND
reservations.departuredate >= $arrival_datetime) OR
(reservations.arrivaldate <= $departure_datetime AND
reservations.departuredate >= $departure_datetime)
)
GROUP BY
r.room_id;
Using the COALESCE function ensures that we get 0 instead of NULL for rooms with no information in the room_prices_table.
Upvotes: 1