Check Availability Query Syntax structure
Posted: Thu Apr 05, 2007 12:00 pm
hello guys i need some help with the following
i have the following tables
display the number of 'rooms' that DO NOT have 'bookings' with 'reservations' between a given date range
i came up with the following
However the bookings are still being shown because of the LEFT JOIN bookings.... So i'm trying to restrict it to show the list of rooms based on
i have the following tables
- bookings - contains info on the rooms that were booked
rooms - info on all the rooms
reservations - info on date range of the reservation
display the number of 'rooms' that DO NOT have 'bookings' with 'reservations' between a given date range
i came up with the following
Code: Select all
SELECT DISTINCT
`rooms`.`RoomNo`
FROM
rooms
LEFT JOIN bookings ON bookings.`RoomNo`
LEFT OUTER JOIN `reservations` ON (`bookings`.`ResvID` = `reservations`.`ResvID`)
WHERE
('2007-04-03' NOT IN (`reservations`.`StartDate`)) AND
('2007-04-03' NOT BETWEEN `reservations`.`StartDate` AND `reservations`.`EndDate`) AND
('2007-04-11' NOT IN (`reservations`.`EndDate`)) AND
('2007-04-11' NOT BETWEEN `reservations`.`StartDate` AND `reservations`.`EndDate`)
- the rooms in the list,
the rooms in the bookings that are not booked between the date range
while excluding the rooms that have been booked within the date range.