> I have 3 tables as follows:
>
> Room Table Reservation Table Check In Table
>
> Fields:
>
> Room_ID Guest_ID Guest_ID
> Room_Type Arrival_Date Check_In_Date
> Departure_Date Departure_Date
> Room_Type Room_Type
> Room_ID
>
> I need to build a query that will tell me how many rooms are available of a
> particular room type for a given range of dates (arrival thru departure) ie:
>
> How many rooms of type "SKJ" are available from
> 8/1/2001 to 8/5/2001.
>

select count(distinct a.Room_id)
from room a, reservation b, CheckIn c
where
a.room_type not in
(select b.room_type from reservation b
where b.Arrival_Date > :Arrival_Date and b.Departure_Date < :Departure_Date
union
select c.room_type from CheckIn c
and c.Check_In_Date > :Arrival_Date and c.Departure_Date < :Departure_Date)

This is easiest to write and probably least effective possible query but it still works. You have to adjust it if your system allows open or
flexible Departure_Date in CheckIn table.

Sincerely,

Ilia Patrik


[Non-text portions of this message have been removed]