MySQL date reservation
===

I try to find a similar question but didn't find an exact one so I'll post mine:

I have 2 tables:

- room (room_id)
- room_reservation (room_id, date)

How can I return list of rooms that have at least 1 day free between 2 dates.

For example, if I have those rooms:

room_id 1, 2 & 3

then in room_reservation table, I have the following:

1 - 2012-07-22
1 - 2012-07-23
1 - 2012-07-24
1 - 2012-07-25
1 - 2012-07-26
2 - 2012-07-23
2 - 2012-07-24

then doing a search for available room between 2012-07-22 and 2012-07-26 should return only room #2 and #3.

I have this query so far but didn't work... please help!

    SELECT DISTINCT room_id AS id
    FROM room
    WHERE (SELECT COUNT('listing') FROM listing_calendar WHERE listing = l.listing_id AND date BETWEEN '2012-07-22' AND '2012-07-26' GROUP BY listing) < 5"

Thanks for the help!
