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`)
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
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.
SELECT *
FROM rooms
WHERE room_id NOT IN
(
SELECT room_id
FROM rooms as innerroom
INNER JOIN bookings USING innerroom.room_id = bookings.room_id
INNER JOIN reservations USING bookings.reservation_id = reservations.reservation_id
WHERE reservations.start <= period.end AND reservations.end >= period.start
);
SELECT *
FROM rooms
WHERE rooms.RoomNo NOT IN
(
SELECT
`bookings`.`RoomNo`
FROM
bookings
LEFT JOIN `reservations` ON (`bookings`.`ResvID` = `reservations`.`ResvID`)
WHERE
('2007-04-03' = reservations.`StartDate`)
OR
('2007-04-03' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
AND
('2007-04-11' = reservations.`EndDate`)
OR
('2007-04-11' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
)
funny when i tried using a "BETWEEN" in my statements it didnt work...I wonder why?
ok so now i want to be able to also check along with rooms that are available within the specified period but also that the number rooms can cater for the number of person the availability is for given that each room can hold 2 persons per room indicated in the table column.
is it that i need to encapsulate the above query as subquery and do a SUM()?
SELECT *,
(SELECT
SUM(rooms.Occupancy)
FROM rooms
WHERE rooms.RoomNo NOT IN
(
SELECT
`bookings`.`RoomNo`
FROM
bookings
LEFT JOIN `reservations` ON (`bookings`.`ResvID` = `reservations`.`ResvID`)
WHERE
('2007-04-03' = reservations.`StartDate`)
OR
('2007-04-03' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
AND
('2007-04-11' = reservations.`EndDate`)
OR
('2007-04-11' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
)
GROUP BY rooms.Occupancy AND rooms.RoomNo) AS NoBeds
FROM rooms
WHERE rooms.RoomNo NOT IN
(
SELECT
`bookings`.`RoomNo`
FROM
bookings
LEFT JOIN `reservations` ON (`bookings`.`ResvID` = `reservations`.`ResvID`)
WHERE
('2007-04-03' = reservations.`StartDate`)
OR
('2007-04-03' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
AND
('2007-04-11' = reservations.`EndDate`)
OR
('2007-04-11' BETWEEN reservations.`StartDate` AND reservations.`EndDate`)
)
can this work? (im talking in terms of efficency and not slowing down the mysql)