Как получить данные, не содержащиеся в таблице?

Question

[alekseyHunter]

Добрый день.

Есть БД, в которой хранятся данные по бронированию аудиторий. В первой таблице хранится время, доступное для выбора. Во второй таблице записывается время из предыдущей таблице и становится забронированным. Требуется получить все промежутки свободного времени.

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

Использовал следующий запрос, но он вытягивает лишнюю первую строку и по результату не понятно в какой именно аудитории есть этот свободный промежуток времени:

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

[AUser0]

Как-то нелогично использование -1/+1, ведь дата начала означает, что в момент начала аудитория УЖЕ недоступна (занята).

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)

Вот так выдаёт действительно незанятые временные промежутки.

Comments

[alekseyHunter]

В этом случае аудитория будет пустовать 30 минут. Т.к. интервала нет

[alekseyHunter]

Хотя можно масштабировать время с 15 до 5 минут

[AUser0]

На каких ID? Запрос покажет свободными ID 5 и 9-10-11-12.
А каков ваш вариант?

Или dataend указывает промежуток, в котором аудитория уже свободна? Ну тогда добавьте в запрос -1. Хотя логичнее хранить в таблице промежутки полной занятости, включительно. Что бы избежать толкований и вот таких -1.

[alekseyHunter]

AUser0, да, если с точки зрения реальности, лучше сделать небольшой перерыв.

[alekseyHunter]

AUser0 А как узнать к какой аудитории относится свободный промежуток?

[AUser0]

Так вы когда делаете запрос - указывайте, для какой аудитории делате поиск.
Соответственно найденные промежутки и будут свободными для указанной аудитории.

[alekseyHunter]

AUser0, а если требуется получить данные для всех свободных аудиторий?

[AUser0]

alekseyHunter, думаю вы сами знаете ответ.

[alekseyHunter]

AUser0, я не могу подписать какой промежуток к какой аудитории принадлежит, поскольку ищу в таблице Date,а не Reservation