SQL Query for hotel room availabilty

Databases
1

Hi

I am working on a hotel booking system. I have a small issue so seeking a help here.

Please consider the table in the screen shot. This is the order table where the customers have placed the orders to book the rooms in the hotel.

My question is, if someone wants to check the availability of a room based on the dates, for example someone wants to book a room from 9th Dec 2010 till 14th Dec 2010, what condition do I have to write in the where clause so that i get a list of booked rooms?

This is what I am doing right now:

WHERE (orders.date_from>='2010-12-09')  AND (orders.date_to<='2010-12-14'))

But it is not fetching the order ID 4 since the “Date_to” is 15th but ideally it should come under booked since the start date meets the condition.

Thanks in advance for any inputs.
PS: I am using mysql 5

2

check out these previous threads…

http://www.sitepoint.com/forums/showthread.php?t=607741

http://www.sitepoint.com/forums/showthread.php?t=664040

notice a pattern?

:slight_smile:

3

Hi

In your post #4, you suggested the same solution which does not work for me.

.. WHERE RangeTo >= START  /* eliminates case 1 */
      AND RangeFrom <= END  /* eliminates case 6 */

Please clarify :slight_smile:

4

sure :slight_smile:

you have to subsitute your own column names and date values


 WHERE Date_To   >= '2010-12-09'   /* eliminates case 1 */
   AND Date_From <= '2010-12-14'   /* eliminates case 6 */
5

Works like a charm!

Many thanks.