Add column from another table matching results from first MySQL query

Question

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 

??

Answer 1

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.