Booking System, MySQL Issue

[chrisrulez001]

Hi,

I'm not sure if I'm going about this the right way, I'm trying to create a booking system which allows you to book hotel rooms but I'm having trouble selecting the rooms that are not booked between a certain date period.

I've attached a picture of my database schema.

Here's what I've got when I query the database specifically for the booked rooms between a certain date period:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    ->  bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> INNER JOIN bookings ON rooms.Room = bookings.Room
    -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-14' AND '2019-02-16';
+------+--------+----------+--------+----------+------------+---------------+
| Room | Type   | Capacity | Adults | Children | ArriveDate | DepartureDate |
+------+--------+----------+--------+----------+------------+---------------+
|    1 | Single |        1 |      1 |        0 | 2019-02-14 | 2019-02-16    |
+------+--------+----------+--------+----------+------------+---------------+
1 row in set (0.00 sec)

If I do a left join but remove the date part of the where clause I get this:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room WHERE roomtype.Adults = 1 AND roomtype.Children = 0;
+------+--------+----------+--------+----------+------------+---------------+
| Room | Type   | Capacity | Adults | Children | ArriveDate | DepartureDate |
+------+--------+----------+--------+----------+------------+---------------+
|    1 | Single |        1 |      1 |        0 | 2019-02-14 | 2019-02-16    |
|    2 | Single |        1 |      1 |        0 | NULL       | NULL          |
|    3 | Single |        1 |      1 |        0 | NULL       | NULL          |
+------+--------+----------+--------+----------+------------+---------------+
3 rows in set (0.00 sec)

Next, I tried doing the left join with the date part in the where clause and I get an empty set:

mysql> SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype on rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room
    -> WHERE roomtype.Adults = 1 AND roomtype.Children = 0 AND bookings.ArriveDate BETWEEN '2019-02-18' AND '2019-02-20';
Empty set (0.00 sec)

Now I assume this problem is down to ArriveDate and DepartureDate of the other entries being null.

I'm not quite sure where to proceed from this point.

[requinix]

Right now your query is going along the lines of taking a room, finding bookings for it, and filtering the results according to the date. By putting the date range in the WHERE you require that all rows match the criteria - that it find bookings in the range. That won't help you find rooms without bookings.

What you need to be doing is taking a room, finding bookings for it in the date range, and filtering the results according to whether there was a booking. With an outer join on the bookings you'll have all rooms with and without bookings in the range, then you use the WHERE to find the room+booking rows that did not actually have a matching booking.

Make sense?

[Barand]

This tutorial may be of help

[chrisrulez001]

Thank you both for your replies.

This is the query I ended up with:

mysql> SELECT rooms.room, roomtype.Type, roomtype.Price, roomtype.Adults, roomtype.Children,
    -> bookings.ArriveDate, bookings.DepartureDate
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-20' AND bookings.DepartureDate > '2019-02-18'
    -> WHERE bookings.Room IS NULL AND roomtype.Adults = 1 AND roomtype.Children = 0 ORDER BY Room;
+------+--------+-------+--------+----------+------------+---------------+
| room | Type   | Price | Adults | Children | ArriveDate | DepartureDate |
+------+--------+-------+--------+----------+------------+---------------+
|    1 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
|    2 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
|    3 | Single | 10.99 |      1 |        0 | NULL       | NULL          |
+------+--------+-------+--------+----------+------------+---------------+
3 rows in set (0.00 sec)

 

[Barand]

I wish I could find a hotel with those prices ?

[chrisrulez001]

Hi,

I recently made a change to my database to make the pricing more efficient.

This is the pricing table:

mysql> SELECT * FROM roomprice;
+--------------+--------+-------+------------+------------+
| RoomPrice_ID | TypeID | Price | FromDate   | ToDate     |
+--------------+--------+-------+------------+------------+
|            1 |      1 | 15.99 | 2019-01-01 | 2019-12-31 |
|            2 |      2 | 10.99 | 2019-01-01 | 2019-12-31 |
|            3 |      1 | 16.99 | 2020-01-01 | 2020-12-31 |
+--------------+--------+-------+------------+------------+
3 rows in set (0.00 sec)

Now if I use my original query but change the query to use the new pricing table I ended up with this query, I put the TypeID column in twice in to check it was matching on both tables:

mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults,
    -> roomtype.Children, roomprice.TypeID, roomtype.TypeID,
    -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
    -> FROM room_facilities
    -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
    -> WHERE room_facilities.Room = rooms.Room) AS Facilities
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24'
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20'
    -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room;
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
| Room | Type   | Cost  | Capacity | Adults | Children | TypeID | TypeID | Facilities                                                 |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
|    4 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
3 rows in set (0.00 sec)

This works however if I alter the pricing table and the pricing of the booking goes into the new pricing range like this:

mysql> SELECT * FROM roomprice;
+--------------+--------+-------+------------+------------+
| RoomPrice_ID | TypeID | Price | FromDate   | ToDate     |
+--------------+--------+-------+------------+------------+
|            1 |      1 | 15.99 | 2019-01-01 | 2019-02-22 |
|            2 |      2 | 10.99 | 2019-01-01 | 2019-12-31 |
|            3 |      1 | 16.99 | 2019-02-23 | 2019-12-31 |
+--------------+--------+-------+------------+------------+
3 rows in set (0.00 sec)

mysql> SELECT rooms.Room, roomtype.Type, ROUND(roomprice.Price * DATEDIFF('2019-02-24', '2019-02-20'), 2) AS Cost, roomtype.Capacity, roomtype.Adults,
    -> roomtype.Children, roomprice.TypeID, roomtype.TypeID,
    -> (SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
    -> FROM room_facilities
    -> INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
    -> WHERE room_facilities.Room = rooms.Room) AS Facilities
    -> FROM rooms
    -> INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
    -> INNER JOIN roomprice ON rooms.TypeID = roomprice.TypeID AND `roomprice`.`ToDate` >= '2019-02-20' AND `roomprice`.`FromDate` <= '2019-02-24'
    -> LEFT JOIN bookings ON rooms.Room = bookings.Room AND bookings.ArriveDate < '2019-02-24' AND bookings.DepartureDate > '2019-02-20'
    -> WHERE bookings.Room IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0 ORDER BY Room;
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
| Room | Type   | Cost  | Capacity | Adults | Children | TypeID | TypeID | Facilities                                                 |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
|    4 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    4 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    5 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 63.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
|    6 | Double | 67.96 |        2 |      2 |        0 |      1 |      1 | Freeview TV, Wi-Fi, Fridge, Tea / Coffee Making facilities |
+------+--------+-------+----------+--------+----------+--------+--------+------------------------------------------------------------+
6 rows in set (0.00 sec)

I end up with it repeating the rows and I've been scratching my head trying to figure it out.

[Barand]

1. You could have a 2 day booking where the price on the first day is different from the price on the second (check in 2019-02-22, check out 2019-02-24) so you cannot just multiply the price by the number of days, you need to get price for each day and SUM() them.

2. You get two records for type 1 rooms because two price records are being matched.

So , match each day that is booked for a booking against the price table to get price for each day, SUM the prices and GROUP BY room_id

I think your booking table is like

+---------------+--------+---------------+----------------+
| booking_id    |  room  |  arrive       |  depart        |
+---------------+--------+---------------+----------------+
|  101          |  1     | 2019-02-22    | 2019-02-24     |  (difficult to find prices for intervening days)
+---------------+--------+---------------+----------------+

I prefer

booking
+---------------+--------+---------------+----------------+
| booking_id    | client |  arrive       |  depart        |
+---------------+--------+---------------+----------------+
|  101          |  123   | 2019-02-22    | 2019-02-24     |
+---------------+--------+---------------+----------------+

room_booking
+--------+---------------+---------------+
|Room_id |  occupied     | booking_id    |
+--------+---------------+---------------+
|   1    | 2019-02-22    |  101          |     (match against price where occupied between the price dates)
|   1    | 2019-02-23    |  101          |
+--------+---------------+---------------+

 

[chrisrulez001]

Yeah, that makes life a lot easier with the dates that are booked in between the arrive date and departure date in a separate table.

Here's the query:

SELECT rooms.Room, roomtype.Type, roomtype.Capacity, roomtype.Adults,
roomtype.Children, Cost,
	(SELECT GROUP_CONCAT(facilities.Facility SEPARATOR ', ')
	FROM room_facilities
	INNER JOIN facilities ON room_facilities.FacilityID = facilities.FacilityID
	WHERE room_facilities.Room = rooms.Room) AS Facilities
FROM rooms
INNER JOIN(SELECT rooms.Room, SUM(roomprice.Price) AS Cost
		   FROM rooms
		   CROSS JOIN temp_room_booking
		   INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
		   INNER JOIN roomprice
		   		ON roomprice.TypeID = rooms.TypeID
				AND night BETWEEN roomprice.FromDate AND roomprice.ToDate
			GROUP BY rooms.Room) calc_price ON calc_price.Room = rooms.Room
LEFT JOIN room_booking ON rooms.Room = room_booking.Room
AND room_booking.occupied >= '2019-03-15' AND room_booking.occupied < '2019-03-19'
INNER JOIN roomtype ON rooms.TypeID = roomtype.TypeID
WHERE room_booking.occupied IS NULL AND roomtype.TypeID = 1 AND roomtype.Adults >= 1 AND roomtype.Children >= 0
ORDER BY rooms.Room;

There was a bit of head scratching before I realised I needed to use a temporary table to work out the cost of the room. 

Thanks again for your help ?