Add column from another table matching results from first MySQL query
- by Nemi
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
??
