CODEWITHSUNDEEP
mysqlsql
Cream Whipped Airplane
Cream Whipped Airplane

Reputation: 1367

Select available rooms in given date range

I am building a hotel booking system and I just got stuck when trying to make a query that retrieves all available room types in a given range.

I have two tables Rooms and Reservations. The Rooms table hold the rooms in the hotel. Their number (id) and their type (type).

Table Reservations holds the reservations made by the clients. The reservation number (id), the associated room number (room_id), and date range (from and to)

I have tried this query:

SELECT room_id as available_room_number, type

FROM roomstesting

LEFT JOIN reservations ON roomstesting.id = reservations.room_id
WHERE reservations.id 
  NOT IN (reservations.e_from <='"2014-03-07 19:00:00' 
           AND reservations.e_to >='2014-03-08 19:00:00')

I was trying to get all the available room types in the range from March 7 to March 8. I was expecting to get the modern room as result of the query. Because modern room id 4 has no reservations made overlapping with the date range and all the other 3 rooms all have reservations made from March 6 to March 9. But I am not getting the result I want. Below is the structure of my database (simplified)

Rooms

| id | type         |
|||||||||||||||||||||
|  1 | master suite |
|  2 | master suite |
|  3 | modern room  |
|  4 | modern room  |

Reservations

| id | room_id | from                | to                  |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|  1 |    1    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
|  2 |    2    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |
|  3 |    3    | 2014-03-05 08:00:00 | 2014-03-09 08:00:00 |

Expected result

| available_room_number | type       |
||||||||||||||||||||||||||||||||||||||
|          4            | modern room|

If anyone here can tell me how I should approach this that would be perfect. Looking forward to your replies.

Upvotes: 4

Views: 3878

Answers (4)

Suryatapa
Suryatapa

Reputation: 39

I always try using BETWEEN and it works

select * from roomstesting 
where room_id not in
(select room_id from roomstesting where date between begin_date and end_date)

Upvotes: 0

ericpap
ericpap

Reputation: 2937

Try this:

SELECT * FROM Rooms WHERE ID NOT IN(SELECT room_id FROM reservations WHERE '2014-03-07 19:00:00' < e_to AND '2014-03-08 19:00:00' > e_from)

Upvotes: 5

Michael Arnell
Michael Arnell

Reputation: 1028

Room 4 doesn't have a reservation, so it's not being picked up by the JOIN. Try something like this:

SELECT    room_id, type
FROM      roomstesting
WHERE     room_id NOT IN (
             SELECT    reservations.room_id
             FROM      reservations
             WHERE     (
                          reservations.e_from <= '2014-03-07 19:00:00' AND
                          reservations.e_to >= '2014-03-08 19:00:00'
                       ) OR (
                          reservations.e_from <= '2014-03-07 19:00:00' AND
                          reservations.e_to < '2014-03-08 19:00:00'
                       ) OR
                       (
                          reservations.e_from > '2014-03-07 19:00:00' AND
                          reservations.e_to >= '2014-03-08 19:00:00'
                       ) OR
                       (
                          reservations.e_from > '2014-03-07 19:00:00' AND
                          reservations.e_to < '2014-03-08 19:00:00'
                       )
                       )

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

Why not just reverse the comparison signs

WHERE reservations.e_from >'2014-03-07 19:00:00' AND reservations.e_to <'2014-03-08 19:00:00'

Upvotes: 1

Related Questions