Overlapping Booking SQL

Question

I have a similiar problem with my sql statement like here Room Booking Query.

the following query works if an apartment has only one booking. but if an apartment has more than one booking, this apartment is also in the result, although it is not available in the requested time range.

SELECT DISTINCT `apartment`.*
FROM `apartment` `apartment`
LEFT JOIN `booking` `booking` ON `apartment`.`uid` = `booking`.`apartment`
WHERE (
    NOT(
        ( `booking`.`start` <= '2018-07-23')
        AND
        ( `booking`.`end` >= '2018-07-21')
    )
)

Can someone help me please to write the right sql?

UPDATE: According the hint of Matt Raines i added a field apartment, with the uid of the apartment, to the booking table. I'm very thankful for any suggestion which helps me to write the right SQL statement!

Here the UPDATED Demo Data:

--
-- Table structure for table `apartment`
--
CREATE TABLE `apartment` (
  `uid` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `bookings` int(10) UNSIGNED NOT NULL DEFAULT '0'
)
--
-- Data for table `tx_apartments_domain_model_apartment`
--
INSERT INTO `apartment` (`uid`, `title`, `bookings`) VALUES
(1, 'Apartment 1', 2),
(2, 'Apartment 2', 1),
(3, 'Apartment 3', 1),
(4, 'Apartment 4', 1);

--
-- Table structure for table `booking`
--
CREATE TABLE `booking` (
  `uid` int(11) NOT NULL,
  `start` date DEFAULT '0000-00-00',
  `end` date DEFAULT '0000-00-00',
  `apartment` int(10) UNSIGNED NOT NULL DEFAULT '0'
)
--
-- Data for table `booking`
--
INSERT INTO `booking` (`uid`, `start`, `end`, `apartment`) VALUES
(1, '2018-07-18', '2018-07-20', 1),
(2, '2018-07-21', '2018-07-23', 1),
(3, '2018-07-18', '2018-07-20', 2);

Try using FIND_IN_SET( `booking`.`uid`, `apartment`.`bookings`) > 0. — skelwa, Jul 16 '18 at 12:00
thanks for your quick answer. but then i git an error ala #1054 - Unknown column 'booking.uid' in 'on clause' — exotec, Jul 16 '18 at 12:07
@exotec what about when it has more than one booking? What is the result? — Bere, Jul 16 '18 at 12:08
@Bere If an apartment has more than one booking, this apartment is also in the result, although it is not available in the requested time range — exotec, Jul 16 '18 at 12:14
@exotec it would be helpful if you can post sample rows for both tables. — Bere, Jul 16 '18 at 12:19
@exotec BTW you should include OR `booking`.`id` is null in the WHERE clause after/before NOT(...) condition to include those apartment with no bookings at all. — Bere, Jul 16 '18 at 12:20
Is there a good reason for using the comma separated list of bookings? Why not just add an `apartment_id` field to the `booking` table? — Matt Raines, Jul 16 '18 at 12:36
@Matt Raines you are right, there is no reason to use commas, but this will not solve my problem ;) if you have a solution with an id field for apartment, this would be very welcome — exotec, Jul 16 '18 at 12:40
Well, it'll make immeasurably simpler, which is a bit like solving it — Strawberry, Jul 16 '18 at 12:52
@Strawberry if a solution with an apartment_id field in the booking table is much easier, then is this also is absolutly ok for me. — exotec, Jul 16 '18 at 12:59
@Bere your suggestion to include OR booking.id is null in the WHERE clause after/before NOT(...) is helpful and has to be added. thank you — exotec, Jul 16 '18 at 13:10
The solution is here: [https://stackoverflow.com/questions/51366904/overlapping-booking-query](https://stackoverflow.com/questions/51366904/overlapping-booking-query) — exotec, Jul 17 '18 at 08:20
The solution is here: [https://stackoverflow.com/questions/51366904/overlapping-booking-query](https://stackoverflow.com/questions/51366904/overlapping-booking-query) — exotec, Jul 17 '18 at 08:22

score 0 · Answer 1 · answered Jul 16 '18 at 18:25

You are off track thinking this has to do with multiple rows from the join. The problem is with your logic in the WHERE clause. You don't say what you are wanting in terms of the dates, so it is impossible to know what the solution should be.

I simplified down to just looking at the booking table. I get the two rows where you are expecting only one. All you need to do is figure out the conditional that you really want.

mysql> SELECT * FROM booking WHERE NOT(start <= '2018-07-23' AND end >= '2018-07-21');
+-----+------------+------------+-----------+
| uid | start      | end        | apartment |
+-----+------------+------------+-----------+
|   1 | 2018-07-18 | 2018-07-20 |         1 |
|   3 | 2018-07-18 | 2018-07-20 |         2 |
+-----+------------+------------+-----------+
2 rows in set (0.00 sec)

Oh, and nice job posting all of the SQL. It makes testing it so much easier for others. — UncleCarl, Jul 16 '18 at 18:26
The solution is here: https://stackoverflow.com/questions/51366904/overlapping-booking-query — exotec, Jul 17 '18 at 08:22

Matt Raines · Answer 2 · 2018-07-17T05:44:15.563

I think you are looking for a list of apartments that do not have any bookings in the date range in question.

Your query, instead, looks for apartments that have at least one booking which is not in the date range in question.

The answers to the question you have linked to should work, but you could also try reversing the question to find apartments that do have a booking in the date range. Then use a LEFT JOIN and a WHERE booking.uid IS NULL to filter out those results.

SELECT apartment.*
FROM apartment
LEFT JOIN booking ON apartment.uid = booking.apartment
                  AND booking.start <= '2018-07-23' AND booking.end >= '2018-07-21'
WHERE booking.uid IS NULL

You might also want to look into adding a foreign key for that booking.apartment field. At the very least, it should be the same datatype as apartment.uid (at the moment one is an INT(10) UNSIGNED and the other is an INT(11)).

The start and end dates for the booking should probably be NOT NULL, unless you can have a booking without dates. And the apartment.bookings field now looks redundant.

The solution is here: https://stackoverflow.com/questions/51366904/overlapping-booking-query — exotec, Jul 17 '18 at 08:23
That's just a link to this question. Did you mean to link to something else? — Matt Raines, Jul 17 '18 at 08:23
this was my mistake. it is same question but with a working solution! — exotec, Jul 18 '18 at 09:14

Overlapping Booking SQL

2 Answers2

Linked