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 22/11/2012 23/11/2012 24/11/2012 25/11/2012 26/11/2012 27/11/2012 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 - roomdetail 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 bookingtable 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