レンタカーや会議室、備品など、開始終了時間を指定して予約可能なものを知りたいことがあります。
以下は会議室予約で考えてみます。
例えば、以下は「2024-06-12 11時」から「2024-06-12 12時」の間に予約可能な部屋のIDと名前を抽出するSQL文です。
SELECT
rooms.room_id,
rooms.room_name
FROM
rooms
LEFT JOIN
reservations ON rooms.room_id = reservations.room_id
AND (
(reservations.start_time < '2024-06-12 12:00:00' AND reservations.end_time > '2024-06-12 11:00:00')
)
WHERE
reservations.room_id IS NULL;以下に解説を加えます。
基本的な考え方としては
予約可能な部屋 ＝ すべての部屋 - 予約できない部屋（予約が重複してしまう部屋）
ということです。
上記をSQL文を使って以下に解説します。
まず、部屋を管理するrooms表と予約を管理するreservations表を作ります。
-- roomsテーブルを作成
CREATE TABLE rooms (
room_id INT AUTO_INCREMENT PRIMARY KEY,
room_name VARCHAR(100)
);
-- reservationsテーブルを作成
CREATE TABLE reservations (
id INT AUTO_INCREMENT PRIMARY KEY,
room_id INT,
start_time DATETIME,
end_time DATETIME
);
それぞれダミーデータを挿入します。
-- roomsテーブルのデータを挿入
INSERT INTO rooms (room_id, room_name) VALUES
(1, 'Conference Room A'),
(2, 'Conference Room B'),
(3, 'Conference Room C'),
(4, 'Conference Room D');
-- reservationsテーブルのデータを挿入
INSERT INTO reservations (room_id, start_time, end_time) VALUES
(1, '2024-06-12 09:00:00', '2024-06-12 10:00:00'),
(2, '2024-06-12 11:00:00', '2024-06-12 12:00:00'),
(3, '2024-06-12 10:00:00', '2024-06-12 11:30:00'),
(1, '2024-06-12 13:00:00', '2024-06-12 14:00:00');
予約状況を図示すると以下の図になります。
まず、LEFT JOINでroomsテーブルとreservationsテーブルの２つのテーブルを結合します。LEFT JOINを使用することで、roomsテーブルに存在するすべての部屋（レコード）が結果に含まれるようにします。つまり、予約がない部屋も結果に含まれるようにします。
SELECT * FROM rooms LEFT JOIN reservations ON rooms.room_id = reservations.room_id;
次に、重複する予約があるレコードを抽出します。以下のSQLの
reservations.start_time < '2024-06-12 12:00:00' AND reservations.end_time > '2024-06-12 11:00:00' という条件は、指定された時間帯（11:00から12:00）の間に重複する予約があるレコードを抽出します。つまり、重複している予約の一覧を取得したことになります。
この時のポイントは２つあります。
1点目は、LEFT JOIN の右側のテーブルに対する条件指定はWHERE句ではなくON句に書くということです。
2点目は、重複する予約があるレコードという条件を
reservations.start_time < '希望レンタル終了日時' AND reservations.end_time > '希望レンタル開始日時'
とすることです。
なぜこの条件で良いのでしょうか？
予約できない時間帯のパターンは下図の3パターンがあり、これ以外にはありません。
いずれも、既存の予約の開始時間よりも希望レンタル終了日時が遅いですね。
また、いずれも、既存の予約の終了時間よりも希望レンタル開始日時が早いですね。
ですので、これで良いのです。
SELECT * FROM rooms LEFT JOIN reservations ON rooms.room_id = reservations.room_id AND (
(reservations.start_time < '2024-06-12 12:00:00' AND reservations.end_time > '2024-06-12 11:00:00')
);
重複している予約の一覧を取得
最後に予約が入っていない、すなわち「room_id IS NULL」のレコードをWhere句を使い抽出します。また、今回必要なのは部屋番号と部屋の名前なので、*を2つの列名に書き換えます。
SELECT
rooms.room_id,
rooms.room_name
FROM
rooms
LEFT JOIN
reservations ON rooms.room_id = reservations.room_id
AND (
(reservations.start_time < '2024-06-12 12:00:00' AND reservations.end_time > '2024-06-12 11:00:00')
)
WHERE
reservations.room_id IS NULL;
指定の日時の間に予約可能な部屋はRoom AとDと分かりました。
以上。