Check Availability Query Syntax structure

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Check Availability Query Syntax structure

Post by kendall »

hello guys i need some help with the following

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
im trying to build a query that will allow me to

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`)
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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Logic: get all the rooms, exclude those that have atleast one reservation in the period... eg:

Code: Select all

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
 );
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

would there be a problem if i was checking a "date" against a "datetime" datatype?

i came up with this...which works...

Code: Select all

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?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

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()?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

ok i have been hacking at it guys and i came up with this...

Code: Select all

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)
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

That looks like a really hairy query.

What are the results of 'explain' with that?
User avatar
kendall
Forum Regular
Posts: 852
Joined: Tue Jul 30, 2002 10:21 am
Location: Trinidad, West Indies
Contact:

Post by kendall »

i did not check it...will get back to you on that though
Post Reply