CODEWITHSUNDEEP
mysql
John
John

Reputation: 1433

MySQL query question: Reservation Like System

Have rooms that may or may not have reservations. Reservations are from(date) to till(date). Person searches for rooms by date: from_field and till_field. Trying to find if a room is available.

SELECT rooms.* FROM rooms
LEFT JOIN reservations ON reservations.room_id = rooms.id
WHERE ( reservations.from > till_field OR reservations.till < from_field )

PROBLEM: If a single reservation succeeds it seems that the query has the room as available, even though another reservation occupies the spot.

Upvotes: 0

Views: 334

Answers (1)

manji
manji

Reputation: 47978

How to retrieve rooms with no resevations at all:

In case there is no reservations, there will be no reservations row returned by the query => you have to check for that also (reservations.room_id IS NULL):

SELECT rooms.* FROM rooms
LEFT JOIN reservations ON reservations.room_id = rooms.id
WHERE reservations.room_id IS NULL -- if there is no reservations
      OR reservations.from > till_field
      OR reservations.till < from_field

But to actually get what ou want, you have to check for rooms that don't have any reservation with:

  • fromDate or tillDate between from_field and till_field

    • OR

  • fromDate < from_field and tillDate > till_field

SELECT rooms.*
  FROM rooms
 WHERE NOT EXISTS (SELECT NULL
                   FROM reservations 
                  WHERE reservations.room_id = rooms.id
                    AND ((
                          reservations.from BETWEEN from_field AND till_field
                          OR
                          reservations.till BETWEEN from_field AND till_field
                         )
                        OR
                         (
                          reservations.from < from_field
                          AND reservations.till > till_field
                         )
                        )
                  )

Upvotes: 3

Related Questions