Sql for room availability

Question

I have following table structure

rooms table

||| room_id ||| name |||
|||    1    ||| best |||
|||    2    ||| best |||
|||    3    ||| best |||
|||    4    ||| best |||

bookings table

||| room_id ||| date_start |||   date_end     |||
|||    1    ||| 2015-01-10 |||  2015-01-15    |||
|||    2    ||| 2015-01-10 |||  2015-01-18    |||
|||    3    ||| 2015-01-05 |||  2015-01-10    |||
|||    4    ||| 2015-01-02 |||  2015-01-05    |||

what i want is lets say if a user search for date_start = 2015-01-10 and date_end = 2015-01-14

I want to show the available rooms for those days.

This is what i have tried so far

SELECT r.*
FROM rooms r
WHERE r.room_id NOT IN (
    SELECT b.room_id FROM bookings b
    WHERE (b.date_start   <= '$data[datestart]' AND b.date_end >= '$data[dateend]'))

thank you!

Answer 1

Look at the possibilities:

x/y = your query's "find available for these days"
a/b = various records' start/end in your db

Here's every possible variant:

   a    b
---+----+---
xy |    |     // no overlap
x  | y  |     // partial overlap
x  |    | y   // complete overlap
   | xy |     // complete overlap
   | x  | y   // partial overlap
   |    | xy  // no overlap

Once you crunch the logic down, you end up with

(y < a) || (x > b)   // no overlap at all

Answer 2

If you are considering a date range such as 01/10 and 01/14, there are three situations where a room is unavailable:

• Someone has a start date between 01/10 and 01/14
• Someone has an end date between 01/10 and 01/14
• Someone has a start date before 01/10 and an end date after 01/14.

So, for the first two you can use the BETWEEN operator to see if dates fall in the range, and for the others use greater than or less than.

What I would suggest, is write a query to get unavailable rooms:

SELECT r.*
FROM room r
JOIN bookings b ON b.room_id = r.room_id
WHERE b.date_start BETWEEN '2015-01-10' AND '2015-01-14'
  OR b.date_end BETWEEN '2015-01-10' AND '2015-01-14'
  OR (b.date_start < '2015-01-10' AND b.date_end > '2015-01-14');

And then you can use NOT IN to select from the rooms_table any rooms that aren't in this forbidden list:

SELECT r.*
FROM room r
WHERE r.room_id NOT IN(
  SELECT r.room_id
  FROM room r
  JOIN bookings b ON b.room_id = r.room_id
  WHERE b.date_start BETWEEN '2015-01-10' AND '2015-01-14'
    OR b.date_end BETWEEN '2015-01-10' AND '2015-01-14'
    OR (b.date_start < '2015-01-10' AND b.date_end > '2015-01-14'));

If you want to avoid the subquery, you can just negate each of your conditions to get available rooms:

SELECT r.*
FROM room r
JOIN bookings b ON b.room_id = r.room_id
WHERE b.date_start NOT BETWEEN '2015-01-10' AND '2015-01-14'
  AND b.date_end NOT BETWEEN '2015-01-10' AND '2015-01-14'
  AND (b.date_start >= '2015-01-10' OR b.date_end <= '2015-01-14');

Here is an SQL Fiddle with all three examples.

Answer 3

SELECT rooms.room_id, rooms.name FROM rooms
INNER JOIN bookings
ON rooms.room_id =bookings.room_id 
WHERE bookings.date_start >= DATE('2015-01-10') AND bookings.date_end <= DATE('2015-01-14');