Как проверить, не использовалось ли конкретное datetime с использованием нескольких таблиц?

Есть 3 стола: здания, номера, бронирования.

1 здание = n номеров
1 комната = n забронированных номеров

ТАБЛИЦЫ - ID (int), name (varchar)
ТАБЛИЦЫ - ID (int), building_id (int)
БРОНИРОВАНИЕ ТАБЛИЦ - ID (int), room_id (int), date_start (datetime), date_end (дата и время)

Пример таблицы зданий

<pre>
ID    name
1     Building A
2     Building B
3     Building C
</pre>

Пример таблицы комнат

<pre>
ID    building_id
1     1
2     1
3     2
4     3
</pre>

Пример таблицы бронирования

<pre>
ID    room_id     date_start            date_end  
1     1           2014-08-09 14:00:00   2014-08-09 14:30:00
2     1           2014-08-09 14:30:00   2014-08-09 15:30:00
3     3           2014-08-09 16:30:00   2014-08-09 17:30:00
4     2           2014-08-09 16:00:00   2014-08-09 17:00:00
5     3           2014-08-09 16:00:00   2014-08-09 16:30:00
</pre>

Вопрос

Как отфильтровать все здания, в которых есть хотя бы 1 комната на определенную дату и время?

Например, мы хотим отфильтровать все здания, в которых есть хотя бы 1 доступная комната с 16:00:00 до 09.08.2014 17:00:00. В этом случае будет показано, что доступны здание A И здание C. В корпусе A есть 1 свободная комната, а в корпусе B также 1 свободная комната, поскольку в этой комнате нет бронирования.

Может ли кто-нибудь помочь мне в этом? Я просто не могу разобраться с одним запросом MySQL, чтобы получить все здания. Спасибо.


mysql php datetime multiple-columns multiple-tables
person user3924829    schedule 09.08.2014    source источник

Ответы (2)


arrow_upward
0
arrow_downward

Используйте LEFT JOIN для соединения таблицы rooms с таблицей reservations с условием времени в предложении on. Затем посмотрите, где нет совпадений - это свободные номера.

SELECT b.id, b.name, COUNT(*) as NumRoomsAvailable
FROM buildings b JOIN
     rooms r
     ON b.id = r.building_id LEFT JOIN
     reservations rv
     ON rv.room_id = r.id AND
        '2014-08-09 16:00:00' BETWEEN rv.date_start AND rv.date_end
WHERE rv.room_id is null
GROUP BY b.id;

Последний шаг - это просто агрегирование по зданию, а не перечисление каждой доступной комнаты отдельно.

person Gordon Linoff    schedule 09.08.2014
comment
Отличный ответ, спасибо! Если можно, у меня есть дополнительный вопрос. Можно ли фильтровать здания только по выбранной дате (если в выбранный день в любой час есть хотя бы 1 доступная комната)? График работы зданий может быть разным. - person user3924829; 09.08.2014
comment
@ user3924829. . . Думаю, это уместнее как отдельный вопрос. Ничто в текущем вопросе не упоминает часы работы здания. - person Gordon Linoff; 09.08.2014
comment
Хорошо, сделаю это. Спасибо. - person user3924829; 09.08.2014

arrow_upward
0
arrow_downward

Попробуйте этот запрос

SELECT b.name,r.id,rv.date_start,rv.date_end FROM buildings b
LEFT JOIN rooms r ON b.id = r.building_id 
LEFT JOIN reservataions rv ON rv.room_id = r.id
WHERE  '2014-08-09 16:00:00' BETWEEN rv.date_start AND rv.date_end
person PravinS    schedule 09.08.2014