SQLの副問い合わせ

質問

MySQLを使って予約の仕組みを作っていますが、SQLの組み立てがわからず困っています。
下にあるように２つのテーブルがあります。
「table_frame」は時間のワクのテーブルです。「table_reserve」は各ワクにリンクされる予約情報です。
１つのワクに対して複数の予約が入ります。
２つのテーブルをジョインして「リスト」を出したいと考えています。
ある１日の予約時間（ワク）の一覧を「リスト」の形式で出力したいと考えています。
１日に対してワクは10個の固定です。

【table_frame】
frameId
reserveDate
reserveTime

【table_reserve】
reserveId
frameId
customerName
number

【リスト　2015-07-15】
ワクID　　　予約タイム　　予約件数
(frameId)　(reserveTime)
1　　　　　　　aa　　　　　　1
2　　　　　　　ab　　　　　　0
3　　　　　　　ac　　　　　　1
4　　　　　　　ad　　　　　　2
・　　　　　　　・　　　　　　・
・　　　　　　　・　　　　　　・
10　　　　　　 aj　　　　　　0

上記の条件で、多分副問い合わせを使うのだと考え、いろいろSQLを組み立ててみましたが、
うまくいきません。
いろいろSQLをいじっていたら訳が分からなくなってしまいました。

以下はその1例です。

【例】
SELECT frameId, reserveTimeZone, rCount
FROM tbl_frame, tbl_reserve
WHERE reserveDate = '2015-07-15'
AND rCount = (SELECT count(*) as rCount FROM tbl_reserve)
AND tbl_frame.frameId = tbl_reserve.frameId

よろしくご指導ください。お願いします。

回答No.2

machi_mac123 · ベストアンサー

こんにちわ。

以下のようなテーブル構造とデータの場合で回答させていただきます。
コンソールからのコピペのため読みにくいところはご容赦ください。

【データ部分】

mysql> select * from frame;
+----------+--------------+--------------+
| frame_id | reserve_date | reserve_time |
+----------+--------------+--------------+
| 1 | 2015-07-15 | 10:20:00 |
| 2 | 2015-07-15 | 10:30:00 |
| 3 | 2015-07-15 | 10:40:00 |
| 4 | 2015-07-15 | 11:40:00 |
| 5 | 2015-07-15 | 12:00:00 |
+----------+--------------+--------------+

mysql> select * from reserve;
+------------+----------+---------------+--------+
| reserve_id | frame_id | customer_name | number |
+------------+----------+---------------+--------+
| 1 | 1 | Tanaka | 1 |
| 2 | 1 | Yoshida | 2 |
| 3 | 1 | Yamada | 3 |
| 4 | 1 | Ota | 4 |
| 5 | 3 | Huguta | 1 |
| 6 | 3 | Tanaka | 2 |
| 7 | 3 | tokorozawa | 3 |
+------------+----------+---------------+--------+

【回答部分】

mysql> select A.frame_id,A.reserve_time,count(B.reserve_id) as count from frame
-> as A left join reserve as B on A.frame_id = B.frame_id
-> where A.reserve_date = "2015-07-15"
-> group by A.reserve_time;
+----------+--------------+-------+
| frame_id | reserve_time | count |
+----------+--------------+-------+
| 1 | 10:20:00 | 4 |
| 2 | 10:30:00 | 0 |
| 3 | 10:40:00 | 3 |
| 4 | 11:40:00 | 0 |
| 5 | 12:00:00 | 0 |
+----------+--------------+-------+

上記でcoonyanp様の求めるデータが取得できてるかなと思うのですが、いかがでしょうか？

ご参考になれば幸いです。

お礼

machi_mac123 さん、回答ありがとうございました。
教えて頂いたSQLでできました。

「left join」は見たこともなかったんで、１時間ほどかかってググって勉強してました。
「外部結合」といって、どちらか表を優先的に出力して、付加的にJOIN先のテーブルの情報（この場合は「count(tbl_reserve.reserveId)」）を出力するのですね。

普通のテーブルJOINは条件に合ったものしか抽出しないんですね。
何気なくいつも使ってましたが、考えてみればそうですね。

半分あきらめかけて、一行一行抽出して、最後にすべての行を結合して組み立てることを考えて取り掛かったんですが、これはこれでやたらに難しく、どうしようか困ってたところだったので、ほんとうに助かりました。

感謝の気持ちでいっぱいです。
ほんとうにありがとうございました

回答No.1

tomato_tea

こんにちは。

実際に動かして試していないので、そのまま動かなかったらスミマセン。

SELECT frameId, reserveTime, count(*) as rCount
FROM tbl_frame, tbl_reserve
WHERE tbl_reserve.reserveDate = '2015-07-15'
AND tbl_frame.frameId = tbl_reserve.frameId
GROUP BY tbl_frame.frameId ,tbl_frame.reserveTime

予約日はWHERE句で条件指定されているので、予約時間でグルーピングしてカウントするとよいと思います。

ご参考になれば幸いです。

お礼

tomato_teaさん,さっそくの回答ありがとうございました。
これでお昼ごはん食べに行ける、と思って以下のSQLを動かしてみました。

SELECT tbl_frame.frameId, reserveTime, count(*) as rCount
FROM tbl_frame, tbl_reserve
WHERE tbl_frame.reserveDate = '2015-07-15'
AND tbl_frame.frameId = tbl_reserve.frameId
GROUP BY tbl_frame.frameId ,tbl_frame.reserveTime

検索できた！　と思ったんですが、予約件数が「1」以上のものしか出力されず、予約件数が「0」のワクは出力されません。ガックシ。

予約件数が「0」のワクも含めてすべて10件のワクを出力するにはどうしたらいいでしょうか。

というか、１行ずつ（１ワクずつ）出力してつなげるしかないでしょうか。

よろしくお願いします。