Odtnhj

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

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 |

Use BETWEEN and AND with your date

SELECT DISTINCT RoomId FROM bookings 
WHERE (date_field BETWEEN From_D AND To_d)

select distinct r.*
from rooms r
 left join bookings b on b.RoomId=r.RoomId
where b.RoomId is null or @arrival > To_D or @departure < from_td

Try this :
 SELECT * FROM rooms room1
 WHERE NOT EXISTS (SELECT *
 FROM booking booking1
 WHERE room1.roomid = booking1.roomid AND
 (:from_d BETWEEN booking1.from_d AND booking1.to_d OR
 :to_d BETWEEN booking1.from_d AND booking1.to_d))