SQL Query returns records which is not required

[vishalonne]

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

 

[towerbase]

You provided this query

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;

strdtver1 and strdtver2 are not valid dates.

Can you give an example of an actual query that failed?

Note that, for MySQL, dates should be in the form 'yyyy-mm-dd' and not 'dd/mm/yyyy'.



Andrew