MariaDB 10.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


10.5.11-MariaDB


create table stables (id int, name varchar(20));





insert into stables values
(864,   'Royale_864' ),
(865,   'Royale_865' ),
(866,   'Royale_866' ),
(867,   'Royale_867' ),
(868,   'Royale_868' ),
(869,   'Royale_869' ),
(870,   'Royale_870' ),
(871,   'Sky View_871'),
(872,   'Sky View_872'),
(873,   'Sky View_873'),
(874,   'Sky View_874'),
(875,   'Sky View_875'),
(876,   'Sky View_876');
CREATE TABLE `event_detail_stable_registrations` (
  `id` int(10) UNSIGNED NOT NULL,
  `accountId` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `stableId` int(10) UNSIGNED NOT NULL DEFAULT '0',
  `checkInDate` TIMESTAMP NOT NULL DEFAULT '1979-01-01 00:00:00',
  `checkOutDate` TIMESTAMP NOT NULL DEFAULT '1979-01-01 00:00:00'
);
INSERT INTO `event_detail_stable_registrations` (`id`,  `accountId`, `stableId`, `checkInDate`, `checkOutDate`) VALUES
(1, 5, 864, '2017-04-27 14:00:00', '2017-04-29 10:00:00'),
(2, 5, 865, '2017-04-25 14:00:00', '2017-04-27 10:00:00'),
(3, 5, 866, '2017-04-27 14:00:00', '2017-05-01 10:00:00'),
(4, 5, 867, '2017-04-27 14:00:00', '2017-05-02 10:00:00'),
(5, 5, 868, '2017-04-29 14:00:00', '2017-05-01 10:00:00'),
(6, 5, 869, '2017-04-30 14:00:00', '2017-05-01 10:00:00'),
(7, 5, 870, '2017-04-30 14:00:00', '2017-05-02 10:00:00'),
(8, 5, 871, '2017-05-01 14:00:00', '2017-05-02 10:00:00'),
(9, 5, 872, '2017-04-27 14:00:00', '2017-05-02 10:00:00'),
(10, 6, 865, '2017-04-23 14:00:00', '2017-04-25 10:00:00'),
(11, 7, 865, '2017-04-27 14:00:00', '2017-04-29 10:00:00');
show create table event_detail_stable_registrations


Table
Create Table


event_detail_stable_registrations
CREATE TABLE `event_detail_stable_registrations` (
  `id` int(10) unsigned NOT NULL,
  `accountId` int(10) unsigned NOT NULL DEFAULT 0,
  `stableId` int(10) unsigned NOT NULL DEFAULT 0,
  `checkInDate` timestamp NOT NULL DEFAULT '1979-01-01 00:00:00',
  `checkOutDate` timestamp NOT NULL DEFAULT '1979-01-01 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=latin1


alter table event_detail_stable_registrations add period for booking(checkInDate, checkOutDate), add primary key (accountId, stableId, booking WITHOUT OVERLAPS)
show create table event_detail_stable_registrations


Table
Create Table


event_detail_stable_registrations
CREATE TABLE `event_detail_stable_registrations` (
  `id` int(10) unsigned NOT NULL,
  `accountId` int(10) unsigned NOT NULL DEFAULT 0,
  `stableId` int(10) unsigned NOT NULL DEFAULT 0,
  `checkInDate` timestamp NOT NULL DEFAULT '1979-01-01 00:00:00',
  `checkOutDate` timestamp NOT NULL DEFAULT '1979-01-01 00:00:00',
  PERIOD FOR `booking` (`checkInDate`, `checkOutDate`),
  PRIMARY KEY (`accountId`,`stableId`,`booking` WITHOUT OVERLAPS)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


insert into event_detail_stable_registrations (id, accountId, stableId, checkInDate, checkOutDate) values (12,5, 874, '2017-04-21 14:00:00', '2017-04-27 14:00:03' )
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)


id
name
status
overLapAccount
start_overlap
end_overlap


864
Royale_864
Not Available
5
2017-04-27 14:00:00
2017-04-29 10:00:00

865
Royale_865
Not Available
7
2017-04-27 14:00:00
2017-04-29 10:00:00

866
Royale_866
Not Available
5
2017-04-27 14:00:00
2017-05-01 10:00:00

867
Royale_867
Not Available
5
2017-04-27 14:00:00
2017-05-02 10:00:00

868
Royale_868
Not Available
5
2017-04-29 14:00:00
2017-05-01 10:00:00

869
Royale_869
Not Available
5
2017-04-30 14:00:00
2017-05-01 10:00:00

870
Royale_870
Not Available
5
2017-04-30 14:00:00
2017-05-02 10:00:00

872
Sky View_872
Not Available
5
2017-04-27 14:00:00
2017-05-02 10:00:00

874
Sky View_874
Not Available
5
2017-04-21 14:00:00
2017-04-27 14:00:03

null
null
Not Available
5
2017-04-25 14:00:00
2017-04-27 10:00:00

null
null
Not Available
5
2017-05-01 14:00:00
2017-05-02 10:00:00

null
null
Not Available
6
2017-04-23 14:00:00
2017-04-25 10:00:00


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
select * from event_detail_stable_registrations


id
accountId
stableId
checkInDate
checkOutDate


1
5
864
2017-04-27 14:00:00
2017-04-29 10:00:00

2
5
865
2017-04-25 14:00:00
2017-04-27 10:00:00

3
5
866
2017-04-27 14:00:00
2017-04-29 14:00:00

3
5
866
2017-04-30 10:00:00
2017-05-01 10:00:00

4
5
867
2017-04-27 14:00:00
2017-05-02 10:00:00

5
5
868
2017-04-29 14:00:00
2017-05-01 10:00:00

6
5
869
2017-04-30 14:00:00
2017-05-01 10:00:00

7
5
870
2017-04-30 14:00:00
2017-05-02 10:00:00

8
5
871
2017-05-01 14:00:00
2017-05-02 10:00:00

9
5
872
2017-04-27 14:00:00
2017-05-02 10:00:00

12
5
874
2017-04-21 14:00:00
2017-04-27 14:00:03

10
6
865
2017-04-23 14:00:00
2017-04-25 10:00:00

11
7
865
2017-04-27 14:00:00
2017-04-29 10:00:00

id	name	status	overLapAccount	start_overlap	end_overlap
864	Royale_864	Not Available	5	2017-04-27 14:00:00	2017-04-29 10:00:00
865	Royale_865	Not Available	7	2017-04-27 14:00:00	2017-04-29 10:00:00
866	Royale_866	Not Available	5	2017-04-27 14:00:00	2017-05-01 10:00:00
867	Royale_867	Not Available	5	2017-04-27 14:00:00	2017-05-02 10:00:00
868	Royale_868	Not Available	5	2017-04-29 14:00:00	2017-05-01 10:00:00
869	Royale_869	Not Available	5	2017-04-30 14:00:00	2017-05-01 10:00:00
870	Royale_870	Not Available	5	2017-04-30 14:00:00	2017-05-02 10:00:00
872	Sky View_872	Not Available	5	2017-04-27 14:00:00	2017-05-02 10:00:00
874	Sky View_874	Not Available	5	2017-04-21 14:00:00	2017-04-27 14:00:03
null	null	Not Available	5	2017-04-25 14:00:00	2017-04-27 10:00:00
null	null	Not Available	5	2017-05-01 14:00:00	2017-05-02 10:00:00
null	null	Not Available	6	2017-04-23 14:00:00	2017-04-25 10:00:00