自身の結果に自身で検索？

質問

php+mysqlにてある予約システムを作っている者です。
mysqlのクエリ作成で行き詰ってしまったので、ご質問させていただきます。

予約テーブルにはカラムid(key)、roomID(部屋番号),startTime(予約開始日時)、endTime(予約終了日時)があります。
部屋テーブルにはカラムid(部屋番号)、部屋名等があります。

実現したいのは、予約日時が被っている部屋id、予約テーブルのidの抽出(重複した予約)です。
一覧画面にて、予約に対して重複があるかないかを表示したいです。
予約テーブルのstartTimeが他の予約開始日時から予約終了日時までの間になければ、
重複予約はないということになると考えています。

しかし行き詰ってしまった原因は、検索対象がない？？ことです。
仮に開始時間だけでもWHERE句に入れられれば、
$検索対象予約開始日時 BETWEEN startTime AND endTime で検索できそうだと想像つくのですが、
その検索対象開始日時は予約テーブル内全部の予約開始日時なのです。

単純に考えると、予約テーブルのidとstartTimeを1件ずつ取得し、全件をBETWEENで検索ループなのでしょうが、
なんとかSQL1文で済ませられないかと思い、質問させていただきました。

疑問点が伝わるか不安な文章で大変申し訳ありませんが、お知恵をお貸しください。

回答No.3

yambejp · ベストアンサー

だいたいやりたいことが分かってきました。

こんなんでどうでしょう？
select t3.id,t3.roomID,t3.startTime,t3.endTime,coalesce(count,0) as count
from tbl as t3
left join(
select t1.id,count(*) as count
from tbl as t1
inner join tbl as t2 on t1.roomID=t2.roomID
where (
t1.startTime >= t2.startTime and t1.startTime < t2.endTime
or t1.endTime > t2.startTime and t1.endTime <= t2.endTime
or t2.startTime >= t1.startTime and t2.startTime < t1.endTime
or t2.endTime > t1.startTime and t2.endTime <= t1.endTime
)
and not t1.id=t2.id
group by t1.id
) as sub on t3.id=sub.id

お礼

完璧でした！
ようやくゴールする事ができました。
理解するのに時間がかかってしまいましたが、
実現することができました。
色々と勉強になることがあり、感謝感謝です。

回答No.2

yambejp

ちょっとやってみました・・・

前の終了時刻と後の開始時刻が一緒でもいいという条件を加えてちょっとやってみました。
create table tbl (id int not null primary key auto_increment ,roomID int, startTime datetime,endTime datetime);
insert tbl values
(1,1,'2011-10-25 10:00:00','2011-10-25 11:00:00')
,(2,1,'2011-10-25 11:00:00','2011-10-25 12:00:00')
,(3,2,'2011-10-25 10:00:00','2011-10-25 11:00:00')
,(4,2,'2011-10-25 10:30:00','2011-10-25 11:30:00')
,(5,2,'2011-10-25 12:00:00','2011-10-25 13:00:00')
,(6,2,'2011-10-25 13:00:00','2011-10-25 14:00:00')
,(7,2,'2011-10-25 13:50:00','2011-10-25 16:00:00')
,(8,2,'2011-10-25 9:00:00','2011-10-25 18:00:00')
;

まず、roomID=1の部屋についてはid=1の終了時刻とid=2の開始時刻が一緒なので
エラーにはなりません。
roomID=2の部屋について、
3と4、6と7がかぶっており、さらにそれらをまとめて8がかぶっているとします。
これを処理するにはこんな感じになります。

select t1.roomID,t1.id as id1,t2.id as id2
from tbl as t1
inner join tbl as t2 on t1.roomID=t2.roomID
where (
t1.startTime >= t2.startTime and t1.startTime < t2.endTime
or t1.endTime > t2.startTime and t1.endTime <= t2.endTime
or t2.startTime >= t1.startTime and t2.startTime < t1.endTime
or t2.endTime > t1.startTime and t2.endTime <= t1.endTime
)
and t1.id<t2.id

もう少し条件はしぼれるかもしれません。

お礼

度々ありがとうございます。
自己結合についてなんとなく理解しました。
前回と今回のご回答をヒントに色々試してみています。
ただ、実現したいことにどんどん近づいているのですが、なかなかゴールになりません。
お恥ずかしいのですが、下記結果を求めるには、ここからどう導き出したら良いのでしょうか。
_______________________________________________________________________
| id | roomID | startTime | endTime | count(重複数) |
--------------------------------------------

tblの全レコード + それぞれのレコードに対しての重複数という感じです。
予約を一覧表示したい為、重複していない予約(今回ならば１と２)のレコードも欲しいのです。

一覧画面を要約して書きますと、
_____________________________________________
| 予約No | 重複予約 | 予約確認 |
----------------------------
| 　 1 　 | 　　 なし 　 | 　 確認 　 |
----------------------------
| 　 2 　 | 　　　あり 　 | 　 　 確認 　 |
----------------------------

将来的には、ここで”確認”を押すことで、予約Noに対応した重複予約を
別画面で表示するようにします。
重複なしの場合は、そのまま予約を確定するという流れです。

度々申し訳ありませんが、お手隙で構いませんので、宜しくお願いします。

回答No.1

yambejp

>実現したいのは、予約日時が被っている部屋id、予約テーブルのidの抽出(重複した予約)です。
>一覧画面にて、予約に対して重複があるかないかを表示したいです。

えーと、少し仕様がわかりづらいです
(1)これは同じ部屋に予約時間が重複してはいけないという意味ですよね？
別の部屋であれば同じ時間帯に予約が入ってよいと思っていいですね？
たとえば、部屋Aと部屋Bがあって
部屋Aの10:00～11:00と部屋Aの10:30～12:00は問題だけど
部屋Aの10:00～11:00と部屋Bの10:30～12:00があっても問題ない
ということですよね？

(2)時間の重複は、同一時刻は含むのでしょうか
部屋Aの10:00～11:00と部屋Aの11:00～12:00は同じ11:00を含みますが
入れ替わりですから問題ないのでしょうか？
それともはち合わせる可能性があるので問題あるのでしょうか？

それをおいておいくとすれば自己結合すればいっぱつですね

select id,roomID
from テーブル as t1
inner join テーブル t2 on t1.roomID=t2.roomID
and not t1.id=t2.id
where t1.starTime between t2.startTime and t2.endTime
or t1.endTime between t2.startTime and t2.endTime

こんな感じ。

お礼

さっそくのご回答ありがとうございます。
仕様について(２)は入れ替わりなので問題ないです。
他はご推察の通りです。
わかりづらくて申し訳ありませんでした。
「自己結合」というのでしたか。
今回いただいたクエリ文を試しながら、自己結合勉強します。
とても助かりました。本当にありがとうございました。