sql - MySql query to find rooms available given dates 'From' and 'to'

Question

Tables are as follows

rooms(RoomId(PK)(int),
      RoomName(varchar))

bookings(RoomId(FK)(int),
         From_D(date),
         To_D(date),
         Bookee(varchar))

(PK-primary Key ,FK-foreign Key)

Given the arrival(From_D) and departure(To_D) dates how do i find out the id of the rooms that are available

I tried:

SELECT DISTINCT RoomId 
FROM bookings 
WHERE  arrival not in(From_D,To_d) AND 
       departure not in(From_D,To_d)

But seeing the structure of my DB problem occurs if there multiple booking of a room stored in the DB, and it books the room even if it was not free in that range of dates

Answer 1

Consider the following diagram (sorry for bad handwriting):

In the above diagram, we can see date ranges on a horizontal axis. From_D to To_D represents a booked slot for a specific room. Four cases are considered for Arrival and Departure dates (denoted by A_i and D_i).

We can clearly see that a particular room is available only when the arrival and departure dates are either completely on the left side, or the right side of the booked slot.

We can GROUP BY on RoomID and consider only those rooms, where every booking for follows the criteria defined above, using the HAVING clause.

The query would be simply the following:

SELECT RoomID 
FROM bookings 
GROUP BY RoomID 
HAVING 
  SUM((From_D < :arrival AND To_D < :arrival) 
             OR 
      (From_D > :departure AND To_D > :departure)) = COUNT(*)

---

Demo on DB Fiddle

CREATE TABLE `rooms` (
 `RoomId` int(4) NOT NULL,
 `RoomName` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`RoomId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `bookings` (
 `RoomId` int(4) NOT NULL,
 `From_D` date NOT NULL,
 `To_d` date NOT NULL,
 `B_Name` varchar(20) NOT NULL,
 KEY `RoomId` (`RoomId`),
 CONSTRAINT `bookings_ibfk_1` FOREIGN KEY (`RoomId`) REFERENCES `rooms` (`RoomId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `rooms` (`RoomId`, `RoomName`) VALUES 
('1', 'Auditorium'), 
('2', 'Room2'), 
('3', 'Room3'), 
('4', 'Room4'), 
('5', 'Room5');

INSERT INTO `bookings` (`RoomId`, `From_D`, `To_d`, `B_Name`) VALUES 
('1', '2018-11-01', '2018-11-03', 'Trance'), 
('2', '2018-11-02', '2018-11-07', 'Alcoding'), 
('3', '2018-11-01', '2018-11-04', 'DebSoc'), 
('4', '2018-11-12', '2018-11-17', 'MunSoc'), 
('5', '2018-11-03', '2018-11-06', 'Pulse');

Query: Check for any availability between 2018-11-01 and 2018-11-03

SELECT RoomId 
FROM bookings 
GROUP BY RoomID 
HAVING 
  SUM((From_D < '2018-11-01' AND To_D < '2018-11-01') 
             OR 
      (From_D > '2018-11-03' AND To_D > '2018-11-03')) = COUNT(*)

Result: Only RoomId 4 is available as per the sample data

| RoomId |
| ------ |
| 4      |