Btukfyl

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