Overlapping Booking Query

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);

thanks for your comment, but this will not solve my problem. or? — exotec, Jul 16 '18 at 17:20

Strawberry · Accepted Answer · 2018-07-16T18:41:35.803

Consider the following.

DROP TABLE IF EXISTS apartment;

CREATE TABLE apartment
(apartment_id SERIAL PRIMARY KEY
,apartment_name varchar(255) NOT NULL 
);

INSERT INTO apartment VALUES
(1, 'Apartment 1'),
(2, 'Apartment 2'),
(3, 'Apartment 3'),
(4, 'Apartment 4');

DROP TABLE IF EXISTS booking;

CREATE TABLE booking 
(booking_id SERIAL PRIMARY KEY
,start_date DATE NOT NULL
,end_date DATE NOT NULL
,apartment_id INT NOT NULL
);

INSERT INTO booking 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);



SELECT a.* 
  FROM apartment a 
  LEFT 
  JOIN booking b 
    ON b.apartment_id = a.apartment_id 
   AND b.start_date <= '2018-07-23' 
   AND b.end_date > '2018-07-21'
 WHERE b.booking_id IS NULL;
+--------------+----------------+
| apartment_id | apartment_name |
+--------------+----------------+
|            2 | Apartment 2    |
|            3 | Apartment 3    |
|            4 | Apartment 4    |
+--------------+----------------+

In practice, unless bound up within a transaction, a SELECT like this is fairly meaningless, because another user can easily come along and make the booking while you're still checking availability.

your query works. thanks alot. but much more interesting is your explanation to the sense :-) you are completely right. If somebody else does a booking at the same time, we got problems. do you have suggestions how to solve this problem? — exotec, Jul 16 '18 at 19:49
It's really about managing the user experience - what do you want to have happen at any given point of the user's interaction with your code. But that's really too big a question for SO, so I'll just give a couple of pointers - one being transactions, as mentioned earlier, and the other.... well...https://stackoverflow.com/questions/30434839/mysql-insert-date-range-into-date-columns-if-dates-dont-overlap-with-existing/30437434#30437434 — Strawberry, Jul 16 '18 at 22:29

Overlapping Booking Query

1 Answers1

Linked

Related