CODEWITHSUNDEEP
mysql
Ralph
Ralph

Reputation: 897

Mysql: Date Comparisons and a Join

I'm trying to run a query to pull available rooms and it joins a reservation table to see available rooms for a given period(start and end date). I'm drawing a blank here and I think this is a pretty simple query, I just can't think of the best way to create it.

What I have so far that needs to be fixed

   SELECT *
     FROM room
LEFT JOIN reservation ON room.id = reservation.room_id
    WHERE reservation.id is null or 
          :startdate > reservation.enddate or 
          :enddate   < reservation.startdate

My above query will return results for rooms with multiple reservations that may satisfy the where clause even though there could be reservation within the date range.

Upvotes: 0

Views: 26

Answers (1)

jpw
jpw

Reputation: 44881

I would do this using a not exists predicate which I find intuitive:

SELECT *
FROM room
WHERE NOT EXISTS (
    SELECT *
    FROM reservation 
    WHERE room.id = room_id
    AND 
      :startdate < enddate 
    AND 
      startdate < :enddate)

The logic says return all rooms for which there doesn't exists any related reservations between the given dates.

Upvotes: 1

Related Questions