How to select all available rooms that are outside the interval choosen by the customer for check-in-CodePudding

ROOM TABLE

CREATE TABLE `room` (
`id` int(11) NOT NULL,
`price` double NOT NULL,
`type` varchar(255) NOT NULL,
`photo` varchar(255) NOT NULL,
`max_capacity` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (1, 25, 'Suite', 'something.jpg', 3);

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (2, 20, 'Single', 'something.jpg', 1);

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (3, 250, 'Family Suite', 'something.jpg', 8);

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (4, 20, 'Twin', 'something.jpg', 2);

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (5, 20, 'Twin', 'something.jpg', 2);

INSERT INTO `room` (`id`, `price`, `type`, `photo`, `max_capacity`) VALUES (6, 25, 'Suite', 'something.jpg', 3);


ALTER TABLE `room`
ADD PRIMARY KEY (`id`);

ORDERS TABLE

CREATE TABLE `orders` (
`id` int(11) NOT NULL,
`checkin` date NOT NULL,
`checkout` date NOT NULL,
`id_user` int(11) NOT NULL,
`id_room` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `orders` (`id`, `checkin`, `checkout`, `id_user`, `id_room`) VALUES
(1, '2023-01-12', '2023-01-13', 1, 1),
(2, '2023-01-11', '2023-01-15', 1, 2);

ALTER TABLE `orders`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
COMMIT;

I tried the following:

SELECT id,type FROM room  WHERE (SELECT \* FROM orders  WHERE  (2023-01-12 NOT BETWEEN  `checkin` AND `checkout`) AND (2023-01-13  NOT BETWEEN `checkin` AND `checkout`));

I expect to get all the room with id (2-6) and the id = 1 to not be showed if the reservation is made in the interval of 12.01.2023(checkin choosen by client x) and 13.01.2023(checkout choosen by client x).

I would appreciate your help alot! THANK YOU!

CodePudding user response：

Try this

SELECT id, type 
FROM room 
WHERE id NOT IN (
    SELECT id_room 
    FROM orders 
    WHERE checkin >= '2023-01-12' AND checkout <= '2023-01-13'
);