need updation for mysql query for choosing date range for reservation of a hotel room or any thing

Question

i had a situation in my project that is as follows.

while checking for the available rooms

$sel_from_bookings="SELECT room_no FROM `booking` WHERE (('".$_POST['req_tdate']."' BETWEEN check_indate AND check_outdate) OR ('".$_POST['req_fdate']."' BETWEEN check_indate AND check_outdate)";

$sel_from_reserv="SELECT room_no FROM `reservation` WHERE (('".$_POST['req_tdate']."' BETWEEN check_indate AND check_outdate) OR ('".$_POST['req_fdate']."' BETWEEN check_indate AND check_outdate))"; 

$sel_rooms="SELECT room_no FROM rooms WHERE room_no NOT IN (".$sel_from_bookings.") AND room_no NOT IN (".$sel_from_reserv.")";

The first query retrives the list of room numbers from the booking table which satisfies the daterange

similarly the second one dos same from the table reservation

the last query uses the list provided by the above two queries and gets the list of room which are not in the generated list.

works fine for 10-08-2010 / 15-08-2010

works fine for 20-08-2010 / 25-08-2010

when i give the dates between 10 and 15 it works fine similarly for 20 and 25 and also works fine for the dates 14-08-2010 and 21-08-2010

but not working for 16-08-2010 to 19-08-2010

need any clarification please ask me.

Thanks.

Answer 1

SELECT  *
FROM    room
WHERE   room_no NOT IN
        (
        SELECT  room_no
        FROM    booking
        WHERE   check_outdate >= @req_fdate
                AND check_indate <= @red_tdate
        )
        AND room_no NOT IN
        (
        SELECT  room_no
        FROM    reservation
        WHERE   check_outdate >= @req_fdate
                AND check_indate <= @red_tdate
        )

Pay attention to the order or the arguments: @req_fdate here is the first date here (from), @req_tdate is the last date (till).

To check for availability from Aug 16 to Aug 19, use this:

SELECT  *
FROM    room
WHERE   room_no NOT IN
        (
        SELECT  room_no
        FROM    booking
        WHERE   check_outdate >= '2010-08-16'
                AND check_indate <= '2010-08-19'
        )
        AND room_no NOT IN
        (
        SELECT  room_no
        FROM    reservation
        WHERE   check_outdate >= '2010-08-16'
                AND check_indate <= '2010-08-19'
        )