SQL for finding free rooms during a specific time period

Question

I'm currently working on a hotel booking service and I want to show all free rooms for a specific roomtype and hotel during a specific time period. I've found what seemed to be a working query until I noticed that: if a room does not have any reservations at all it doesn't work.

SELECT DISTINCT r.id, 
            r.num, 
            r.NAME, 
            h.NAME, 
            h.city, 
            h.country 
FROM   room r 
   JOIN hotel h 
     ON r.hotel_id = h.id 
   JOIN room_type rt 
     ON r.room_type_id = rt.id 
WHERE  r.id NOT IN (SELECT room_id 
                    FROM   reservation 
                    WHERE  ( '$from' BETWEEN start_date AND end_date ) 
                        OR ( start_date BETWEEN '$from' AND '$to' ) 
                        OR '$from' = start_date) 
   AND $roomtype = rt.id 
   AND $hotel = h.id

This query will be used in a PHP script where "$from" is the user's chosen start of reservation date and $to is end of reservation etc.

Is there a way to make this query work even if a room has no reservations? Any help is greatly appreciated!

Gordon Linoff Gordon Linoff · Accepted Answer · 2017-01-28T18:14:46

The logic for a not-free room is:

The booking starts on or before the period in question ends.
The booking ends on or after the period in question starts.

A not not-free room is available. Let's apply this logic to your query:

SELECT r.id, r.num, r.name, h.name, h.city, h.country
FROM room r JOIN
     hotel h
     ON r.hotel_id = h.id JOIN
     room_type rt
     ON r.room_type_id = rt.id
WHERE rt.id = $roomtype AND
      h.id = $hotel AND
      r.id NOT IN (SELECT res.room_id 
                   FROM reservation res
                   WHERE '$from' <= end_date AND
                         '$to' >= start_date AND
                  );

Note: You should be using parameters for values in your query, rather than munging query strings.

SQL for finding free rooms during a specific time period

1 Answers