Assume you want to check the availability of a room between two dates. You
only want to get the rooms that are available for the entire time from
@startdate to @enddate
set @startdate='some_start_date', @enddate='some_end_date'
SELECT r.room_number, r.room_type
FROM rooms r
LEFT JOIN reservations rv
on rv.room_ID = r.id
AND rv.startdate <= @enddate
AND rv.enddate >= @startdate
WHERE rv.id is null
NOTE: the time portion of @startdate should be 00:00:00 on the first day
and the time portion of @enddate should be set to 23:59:59 for the last
day of the window you are interested in.
How this works:
The ON conditions of the JOIN of the table rooms to the table reservations
identifies any reservation that covers any portion of the window you are
interested in (draw it out with a number line, you will see the logic
works)
But because it's a LEFT JOIN (not an INNER JOIN) it lists all of the rooms
regardless of whether or not there is a reservation. So, for those rows
with room information but without reservation information to match our ON
conditions, all of the columns that would normally have reservation data
in them will have null values. We only want those unmatched rows so that's
why we wrote the WHERE to return only those where the rv.id is null but we
could have checked for NULL in any reservation column.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Giulio <[EMAIL PROTECTED]> wrote on 07/29/2004 09:18:50 AM:
> Hi all,
> I'm using two tables for handling a little room reservations system:
>
> a rooms table:
>
> CREATE TABLE `rooms` (
> `id` int(11) NOT NULL auto_increment,
> `room_number` int(3) default NULL,
> `room_type` char(1) default NULL,
> PRIMARY KEY (`id`)
> ) TYPE=MyISAM;
>
> a reservations table:
>
> CREATE TABLE `reservations` (
> `id` int(11) NOT NULL auto_increment,
> `room_id` int(11) default NULL,
> `date_from` date default NULL,
> `date_to` date default NULL,
> PRIMARY KEY (`id`)
> ) TYPE=MyISAM;
>
> I have quite clear the join syntax (using for the the ON clause
> rooms.id and reservations.room_id fields ) to list all the rooms
> reserved on a given date interval, but I can't figure out the query to
> list all the rooms of a given type (room_type) that results FREE ( not
> reserved ) on the given date interval.
>
> thanx for your help,
>
> Giulio
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
