Hi Everybody

I am trying to list records of those rooms which are not booked between two dates. I have 2 tables
roomtable
"Field","Type","Null","Key"
"room_no","varchar(3)","NO","PRI"
"room_type","varchar(10)","NO"
"room_rate","int(4)","NO"
"room_bed","varchar(6)","NO"
bookingtable
"Field","Type","Null","Key"
"book_id","varchar(3)","NO","PRI"
"room_no","varchar(3)","YES"
"date_fro","datetime","YES"
"date_to","datetime","YES"
"no_of_adults","int(3)","YES"
"no_of_child","int(3)","YES"

I tried this query to got my result and I got the list of all rooms which are not booked -
SELECT roomdetail.room_no,room_type,room_bed,room_rate FROM roomdetail LEFT JOIN bookingtable
ON roomdetail.room_no=bookingtable.room_no
WHERE bookingtable.room_no IS NULL;

I tries this also but I got blank output-
SELECT roomdetail.room_no,room_type,room_bed,room_rate FROM roomdetail LEFT JOIN bookingtable
ON roomdetail.room_no=bookingtable.room_no
WHERE DATE(date_fro)>='2012-01-08' AND DATE(date_to)<='2012-01-11' AND bookingtable.room_no IS NULL;
Can any one please help me to solve this problem.

Thank you to all for cooperation.
Regards

this might help...
Absences between a date range...

I was going to find the same link that Rudy posted

I draw out Rudy's diagram near the foot of that page almost every time I have to solve a problem like this