Get the detail of overlapping bookings mysql

Question

I am developing a stable booking system where user can book and update their room bookings and chose stables from interactive map.

My stable registration db structure is like below

event_detail_stable_registrations
| id | accountId | eventId | stableId | checkInDate | checkOutDate | 
  5     233         55         66       26-06-2017    28-06-2017
  6     234         55         66       28-06-2017    29-06-2017

When user updates the booking but do not change the checkInDate and checkOutDate then its an easy scenario which i have implemented already.

In above case if user 234 updates the booking and change checkInDate then the query should return 233 for stableId 66 but my query returns '234' as accountId

Another scenario is when user changes the checkInDate and or checkOutDate of the registration. User A wants to change the booking detail how can i check if any overlapping for updated checkInDate and or checkOutDate for user's booking and if those are already booked then which accountId has booked it.

Right now I am running following query which gives me correct information about overlapping dates but could not get the information of account who has booked it.

Query always returns the user's accountId for overlapping dates as well.

 SET @checkInDate = '2017-04-27 14:00:00' , @checkOutDate = '2017-05-01 10:00:00' ; 

SELECT a.*,
IF(b.`stableid` IS NULL,"Avalalable","Not Available") as `status`,
IF(NOT b.`stableid` IS NULL,b.`accountId`,"") as `overLapAccount`,
IF(NOT b.`stableid` IS NULL,b.`checkInDate`,"") as `start_overlap`,
IF(NOT b.`stableid` IS NULL,b.`checkOutDate`,"") as `end_overlap`
FROM `event_detail_stable_registrations` b
LEFT JOIN `stables` a
ON a.`id` = b.`stableid` AND
 (((`checkInDate` BETWEEN @checkInDate AND @checkOutDate)
  OR (`checkOutDate` BETWEEN @checkInDate AND @checkOutDate))
   OR ((@checkInDate BETWEEN `checkInDate` AND `checkOutDate`) 
     OR (@checkOutDate BETWEEN `checkInDate` AND `checkOutDate`))
)
ORDER BY a.`name`

Here is the SQL fiddle where I haven't used the same DB structure but its similar.

The output I get for the same stable booked by multiple account for given period is fine but with the query I am using, I get null in the column name,stableId.

user_10195153 · Answer

Part 2 of your scenario:

MariaDB-10.5 Application Time Periods - WITHOUT OVERLAPS can enforce the constraints of non-overlapping bookings:

ALTER TABLE event_detail_stable_registrations
ADD period FOR booking(checkInDate, checkOutDate),
ADD PRIMARY KEY (accountId, stableId, booking WITHOUT OVERLAPS)

A user cancelling a day is just:

DELETE
FROM event_detail_stable_registrations
FOR PORTION OF booking
FROM '2017-04-29 14:00:00' TO '2017-04-30 10:00:00'
WHERE accountId=5
  AND stableId=866

Which splits the non-deleted dates booking entry over periods.

Any UPDATE of a booking enforces the constraints.

ref: dbfiddle

Get the detail of overlapping bookings mysql

1 Answers