Hi All
I am trying to write a Hotel Room Booking System using Java and MySQL.

I want to select those records only which are available between a range of dates.
Suppose 112 is book for 5 days say from 22/11/2012 to 27/11/2012, then this record of room no. 112 must not be diaplayed for these dates
Whether I select date range from 24/11/2012 to 26/11/2012 or from 21/11/2012 to 25/11/2012 or from 25/11/2012 to 27/11/2012. I my sql query is not performing what I want
SELECT roomdetail.room_no, room_type, room_bed, room_rate FROM roomdetail LEFT JOIN bookingtable ON roomdetail.room_no=bookingtable.room_no AND DATE(date_fro) >= 'strdtver1' AND DATE(date_to) <= 'strdtver2' WHERE bookingtable.room_no IS NULL;
Above query displays the room 112 which is already booked if I select dates range given in example.
Table structure -
Field Type Null Key Default Extra
room_no varchar(3) NO PRI NULL
room_type varchar(10) NO NULL
room_rate int(4) NO NULL
room_bed varchar(6) NO NULL

Field Type Null Key Default Extra
book_id int(3) NO PRI None
room_no varchar(3) YES NULL
date_fro datetime YES NULL
date_to datetime YES NULL
no_of_day int(3) YES NULL
I trying to do this by this way but not successfull

I need guidance and support pls help