Retrieving specific tuples using Mysql

Question

Hi, I have some problems retrieving specific tuples. I am actually a student trying to build a Room management system. I have two tables:

Room(roomID,hotelname,rate) 

and

Reservation(resID,arriveDate,departDate,roomID).

I am not sure how to retrieve the rooms that are available between 2 specific dates. This was the query that i used.

SELECT Room.roomID,hotelname,rate 
  FROM Room 
  LEFT JOIN Reservation 
    on (     Room.roomID=Reservation.resID 
         and arriveDate >='2010-02-16' 
         and departDate <='2010-02-20'
       ) 
 GROUP BY roomID,hotelname,rate 
HAVING count(*)=0;'

but it returns an empty set. Can any1 be kind enough to tell me what mistake i am doing??

Answer 1

I guess Room.roomID=Reservation.resID should be Room.roomID=Reservation.roomID.

You could try a different approach with a subselect:

SELECT roomID,hotelname,rate 
FROM Room 
WHERE roomID NOT IN (SELECT roomID FROM Reservation WHERE arriveDate >='2010-02-16' and departDate <='2010-02-20')