left join？で実現できそうなこのクエリを一行で書いてみたい。

質問

left joinについてお聞きします。一行で実現できるのかどうか、興味があります。

student
| id | name | class |
| 1 | aaa | 1 |
| 2 | bbb | 3 |
| 3 | ccc | 4 |
| 4 | ddd | 3 |
| 5 | eee | 1 |

iinkai
| id | student.id | name | meeting |
| 1 | 1 | tosho | 2006-11-30 |
| 2 | 2 | souji | 2006-12-02 |
| 3 | 2 | siiku | 2006-12-03 |
| 4 | 3 | tosho | 2006-11-30 |

ルール
・studentからclassが3,4,5の生徒を抽出
・iinkaiに所属していなくても生徒は抽出
・所属していても、全てのmeetingが既に終わっていればいらない
・複数のiinkaiに所属している場合は、2006-12-01以降にmeetingが行われるもので直近のinnkaiを表示する

結果として欲しいテーブルは

| student.id | class | iinkai.id | iinkai.name | meeting |
| 2 | 3 | 2 | souji | 2006-12-02 |
| 3 | 4 | null | null | null |
| 4 | 3 | null | null | null |

文字数が足りません。ご教授お願いします。

回答No.4

yambejp · ベストアンサー

＃２です

命題にブレがありますね・・・
すでにすぎた委員会のミーティングを無視するのであれば、
3行目4行目はnull値を返さずに、単純に1行目のみ参照されるはずです。
仕様をよくよく検討した上で、設計なさるとよいでしょう。

さて、それではどうやって
| student.id | class | iinkai.id | iinkai.name | meeting |
| 2 | 3 | 2 | souji | 2006-12-02 |
| 3 | 4 | null | null | null |
| 4 | 3 | null | null | null |
を返せばいいかというと、テンポラリをつかって以下の様にします。

CREATE TEMPORARY TABLE `temp_date`
SELECT `student_id`,min(`meeting`) as `meeting`
FROM `iinkai`
WHERE `meeting`>'2006-12-01'
GROUP BY `student_id`;

SELECT `student`.`id` , `class` , `iinkai`.`id` , `iinkai`.`name` , `iinkai`.`meeting`
FROM `student`
LEFT JOIN `temp_date` ON `temp_date`.`student_id`=`student`.`id`
LEFT JOIN `iinkai` ON `temp_date`.`student_id`=`iinkai`.`student_id`
AND `temp_date`.`meeting`=`iinkai`.`meeting`
WHERE `class` IN (3,4,5);

これであれば3.23系でも動作します

回答No.3

chukenkenkou

#1回答者です。

MySQL 4.x系でも書けそうなSQLに変えてみました。
一時表（テンポラリ・テーブル）を作成し、その表を結合することで実現しています。

実行日以降で実行日に直近は、

(1)実行日以降
　例では、「meeting>='2006-12-01'」。
(2)s_id（iinkai表の生徒id）でグループ化
　group by s_id
(3)meetingの最小値
　min(meeting)

を組み合わせることで求められます。

１．準備
(1)表定義及びデータ格納
create table student
(`id` tinyint,
`name` varchar(16),
`class` tinyint);
insert into student values
(1,'aaa',1),
(2,'bbb',3),
(3,'ccc',4),
(4,'ddd',3),
(5,'eee',1);

create table iinkai
(`id` tinyint,
s_id tinyint,
`name` varchar(16),
meeting date);
insert into iinkai values
(1,1,'tosho','2006-11-30'),
(2,2,'souji','2006-12-02'),
(3,2,'siiku','2006-12-03'),
(4,3,'tosho','2006-11-30');

２．実行
(1)一時表の定義
create temporary table w_stu
select s_id,min(meeting) as meeting
from iinkai
where meeting>='2006-12-01'
-- where meeting>=current_date
group by s_id;

(2)検索
select x.`id`,x.`class`,y.`id`,y.`name`,y.meeting
from student as x
left join (iinkai as y
inner join w_stu as z
on y.s_id=z.s_id and y.meeting=z.meeting)
on x.`id`=y.s_id
where `class` in(3,4,5);

回答No.2

yambejp

命題が意味不明。

まずiinkaiテーブルのstudent.id フィールドは紛らわしいので
student_idとしておきましょう。

そこで順番に
>・studentからclassが3,4,5の生徒を抽出
select * from student
where class in (3,4,5)
ですね。

>・iinkaiに所属していなくても生徒は抽出
select * from student
left join iinkai on student.id=student_id
where class in (3,4,5)
ですね。

>・所属していても、全てのmeetingが既に終わっていればいらない
意味がわかりません。
「終っている」というフラグもないですし、今日の日付けと
くらべればいいのでしょうか？
終っていない場合は表示しないのでしょうか？
それなら
select * from student
left join iinkai on student.id=student_id
where class in (3,4,5)
and meeting<CUREDATE()
みたいな感じ。

NULLで表示するのでしょうか？
そうなるとテンポラリをleft joinする形になりそうですが・・・

>・複数のiinkaiに所属している場合は、2006-12-01以降にmeetingが
>行われるもので直近のinnkaiを表示する
複数ではない場合は2006-12-01以降という制限は無視してよいのでしょうか？
また、前項と競合する場合はどちらが優先するのでしょうか？

そもそもMySQLのバージョンはいくつですか？
それによって書き方はかなりちがってきます。
3.23系であれば、テンポラリをつかってもよいですか？

補足

>>・所属していても、全てのmeetingが既に終わっていればいらない
>意味がわかりません。

えっと、説明不足でした。すいません。
このSQLを発行する日にちを仮に「2006-12-01」であるとしたときに、meetingがその日以前に行われたiinkaiは考慮しないということです。
そして直近の意味としては、この日（2006-12-01）以降で一番最初にmeetingが開かれるものを抽出したいという意味でした。

>>・複数のiinkaiに所属している場合は、2006-12-01以降にmeetingが
>>行われるもので直近のinnkaiを表示する
>複数ではない場合は2006-12-01以降という制限は無視してよいのでしょうか？
>また、前項と競合する場合はどちらが優先するのでしょうか？

単数、複数に限らず既に終了しているmeetingは無視します。その日以降に開催されるmeetingで生徒が所属していて、一番最初に開催されるものをその生徒の行に表示します。その日以降、その生徒が参加しなくてはならないmettingが複数・単数に関わらずです。

ちなみにmysqlのバージョンは、4.0.26でした。レンタルサーバなのでヴァージョンは変えられないので、このヴァージョンに対応したものをお願いします。

最後のルール「その日以降で直近に開催されるものを１つだけ表示する」というのが難しくて、分からなかったので、ここに関してもう少し教えていただけたらと思いました。

不可能なら不可能ということを知りたい、という気持ちです。

回答No.1

chukenkenkou

>一行で実現できるのかどうか、興味があります

1文という意味ですか？
条件が明確なら、できると思いますが？

文章でだらだらと書かれても、意味が通じません。
あなたが考えたSQLは、ないのですか？

>所属していても、全てのmeetingが既に終わっていればいらない

どういう意味ですか？

iinkai表のidとnameの対応が取れていませんが、合っているのですか？

>複数のiinkaiに所属している場合は、2006-12-01以降にmeetingが行われるもので直近のinnkaiを表示

直近だと、2006-12-03が検索されるべきでは？
最も古いものの誤りですか？

不明な点が多いですが、例えば以下のような感じです。MySQL 5.0以降なら
動くと思います。

【SQL例】
select x.`id`,`class`,y.`id`,y.`name`,`meeting`
from
(select *
from student
where `class` in(3,4,5)) as x
left join
(select * from iinkai
where (s_id,`meeting`) in
(select s_id,min(`meeting`) as `meeting`
from iinkai
where `meeting`>='2006-12-01'
group by s_id)) as y
on x.`id`=y.s_id;

お礼

この度は丁寧に教えていただきありがとうございました。無事必要な機能を実装できました。ありがとうございました。

補足

文章が分かりづらくて、もうしわけありませんでした。
テーブルも簡易的に書いたもので、実際のDBはもう少し複雑ですが、問題となっている「特定の日付以降でmeetingがあればその中で一番最初に行われるものを1つとってくる」という部分をクリアにしたつもりでした。

当方のmysqlのバージョンは4.0系ということで上記のものは動かないのですか？5系以降でどういう機能が追加されたのかはわかないのですが、fromの後にselect文でテーブルを用意するという部分のことだと思いました。
もし可能なら４．０系のSQLで教えていただけるとうれしいです。

とりあえず上記のものを書いてみたいのですが、エラーがでてしましました。
よろしくお願いします。