How to get data not contained in a table?

Question

MySQL

Good afternoon.

There is a database that stores data on booking audiences. The first table stores the time available for selection. The second table records the time from the previous table and becomes booked. It is required to get all the intervals of free time.

www.sqlfiddle.com/#!9/aa82ab/1/0

CREATE TABLE `date` (
  `hour_id` int(11) NOT NULL,
  `minutes_id` int(11) NOT NULL,
  `id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `reservation` (
  `id` int(11) NOT NULL,
  `classroom_id` int(11) NOT NULL,
  `datestart_id` int(11) NOT NULL,
  `dateend_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `reservation` (`id`, `classroom_id`, `datestart_id`, `dateend_id`) VALUES
(1, 340, 1, 4),
(2, 340, 6, 8);

INSERT INTO `date` (`hour_id`, `minutes_id`, `id`) VALUES
(1, 1, 1),
(1, 2, 2),
(1, 3, 3),
(1, 4, 4),
(2, 1, 5),
(2, 2, 6),
(2, 3, 7),
(2, 4, 8),
(3, 1, 9),
(3, 2, 10),
(3, 3, 11),
(3, 4, 12);

I used the following query, but it pulls out the extra first line and it is not clear from the result which audience has this free time period:

Select * From date d where d.id not in(
SELECT d.id FROM date d, reservation r WHERE d.id BETWEEN r.datestart_id+1 and r.dateend_id-1)

Answer 1

It is somehow illogical to use -1/+1, because the start date means that the audience is ALREADY unavailable (busy) at the start time.

Select * From date d where d.id not in(
SELECT d.id FROM date d, reservation r WHERE d.id BETWEEN r.datestart_id and r.dateend_id)

This is how it gives out really unoccupied time intervals.