1

I have 3 table in my sql database:

Table Room

roomnumber   roomtype    
  1           DeluxeRoom           
  2           DeluxeRoom  
  3           DeluxeRoom
  100         StandardRoom           
  101         StandardRoom  
  102         StandardRoom

Room is a table which store the room and their type.

Table Reservation

reservationid  checkin       checkout
  1           2017-01-01     2017-01-03
  2           2017-01-10     2017-01-20
  3           2017-02-05     2017-02-08

Reservation is a table where users make their booking.

Table RoomReserve

reservationid    roomnumber
 1                 1
 2                 100
 2                 101
 3                 1
 3                 2
 3                 3

In RoomReserve, for each reservationid, I can input multiple values for roomnumber. I can also input same roomnumber for different reservationid if the checkin and checkout date are not the same.

I am trying to make a query to count the available rooms when user want to book rooms but the results i am getting is wrong.

Here are the codes i am trying:

SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
        SELECT  *
            FROM  roomreserve r,reservation e, room m
            WHERE  r.reservation_id=e.reservation_id
              AND  m.roomnumber=r.roomnumber
              AND  ('$checkin' BETWEEN start_date AND end_date)
              OR  ('$checkout' BETWEEN start_date AND end_date)
                          );
2) 
SELECT  COUNT(roomnumber)
    FROM  room
    WHERE  roomtype='DeluxeRoom'
      AND  roomnumber NOT IN (
SELECT  COUNT(roomnumber)
    FROM  roomreserve r,reservation e, room m
    WHERE  r.reservation_id=e.reservation_id
      AND  m.roomnumber=r.roomnumber
      AND  ('$checkin' BETWEEN start_date AND end_date)
      OR  ('$checkout' BETWEEN start_date AND end_date)
                          );
  • The problem you're dealing with is called relational division, and there are good articles about it here and here. – Erik Darling Jan 14 '18 at 1:43
2

This is a somewhat common logical error.

('$checkin' BETWEEN start_date AND end_date) OR
('$checkout' BETWEEN start_date AND end_date)); 

The problem is not the way the query is written, it's that you're using the wrong criteria. A reservation can conflict while neither of these is true, simply by starting before and ending after the desired values.

What you actually need to find is the overlap.

A room is not available if it has any existing reservation with the following conditions true:

  • a check-in before your desired check-out and
  • a check-out after your desired check-in

Any reservation that starts before your desired end and ends after your desired start is a conflict.

Stated another way, a check-in after your desired check-out cannot possibly be a conflict, and a check-out before your desired check-in cannot possibly be a conflict. All others are conflicting.

So you want to identify these reservations:

(end_date > '$checkin') AND
(start_date < '$checkout'); 

Note also that you must never build queries with variable interpolation, as you have done here. It is an unsafe practice for which there are no genuine exceptions, only excuses. Use prepared statements and placeholders.

0

Don't use either of these

NOT IN ( SELECT COUNT(...) ... )
NOT IN ( SELECT * ... )

Instead, use one of these:

NOT IN ( SELECT roomnumber ... )
NOT EXISTS ( SELECT * ... WHERE ... )
LEFT JOIN ... WHERE roomnmber IS NULL

Simpler expression for checking for overlap:

checkin < end AND checkout > start

(I did not check the rest of the logic.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.