内部結合やら左外部結合やらの備忘録

色々INNER JOINして、最後にLEFT JOINで点数別のコメントを追加する。

論理名	テーブル名	下限フィールド名	上限フィールド名	参照値
合計点数	comment	min	max	examinee.point
区分1の点数	comment_division_1	min	max	examinee.point_div1
区分2の点数	comment_division_2	min	max	examinee.point_div2
区分3の点数	comment_division_3	min	max	examinee.point_div3
区分4の点数	comment_division_4	min	max	examinee.point_div4

上記の各テーブルはレコードが複数あり、参照値が下限と上限に収まるレコードのcommentフィールドを参照する。
comment_division_1〜4テーブルはcomment_divisionテーブルをdivisionフィールドの値毎別に一時テーブル化したもの。

SELECT
examinee.no                AS examinee__no,
examinee.id                AS examinee__id,
examinee.point             AS examinee__point,
examinee.point_div1        AS examinee__point_div1,
examinee.point_div2        AS examinee__point_div2,
examinee.point_div3        AS examinee__point_div3,
examinee.point_div4        AS examinee__point_div4,
examinee.flgPayment        AS examinee__flgPayment,
examinee.flgPassing        AS examinee__flgPassing,
exam_detail.no__question   AS exam_detail__no__question,
user.noAS user__no,
user.idAS user__id,
user.name_sei              AS user__name_sei,
user.name_mei              AS user__name_mei,
user.kana_sei              AS user__kana_sei,
user.kana_mei              AS user__kana_mei,
user.mail                  AS user__mail,
user.tel                   AS user__tel,
user.zip                   AS user__zip,
user.mail                  AS user__mail,
user.tel                   AS user__tel,
user.zip                   AS user__zip,
user.kenNo                 AS user__kenNo,
user.city                  AS user__city,
user.address               AS user__address,
user.mansion               AS user__mansion,
user.sex                   AS user__sex,
user.birth                 AS user__birth,
user.memo                  AS user__memo,
user.flgClass1             AS user__flgClass1,
user.flgClass2             AS user__flgClass2,
user.flgClass3             AS user__flgClass3,
user.flgClass4             AS user__flgClass4,
user.flgClass5             AS user__flgClass5,
user.flgClass6             AS user__flgClass6,
user.flgClass7             AS user__flgClass7,
user.flgClass8             AS user__flgClass8,
groups.name                AS groups__name,
hall.name                  AS hall__name,
hall.kana                  AS hall__kana,
hall.form                  AS hall__form,
hall.type                  AS hall__type,
hall.tel                   AS hall__tel,
hall.zip                   AS hall__zip,
hall.kenNo                 AS hall__kenNo,
hall.city                  AS hall__city,
hall.address               AS hall__address,
hall.mansion               AS hall__mansion,
hall.memo                  AS hall__memo,
question.line              AS quetion__line,
comment.comment,
comment_division_1.comment AS comment_div1,
comment_division_2.comment AS comment_div2,
comment_division_3.comment AS comment_div3,
comment_division_4.comment AS comment_div4
FROM examinee
INNER JOIN exam_detail        ON examinee.no__exam_detail = exam_detail.no
INNER JOIN exam               ON exam.no                  = exam_detail.no__exam
INNER JOIN user               ON user.no                  = examinee.no__user
INNER JOIN groups             ON groups.no                = examinee.no__groups
INNER JOIN hall               ON hall.no                  = exam_detail.no__hall
INNER JOIN question           ON question.no              = exam_detail.no__question
LEFT  JOIN comment            ON question.no              = comment.no__question            AND examinee.point      BETWEEN comment.min            AND comment.max
LEFT  JOIN comment_division_1 ON question.no              = comment_division_1.no__question AND examinee.point_div1 BETWEEN comment_division_1.min AND comment_division_1.max
LEFT  JOIN comment_division_2 ON question.no              = comment_division_2.no__question AND examinee.point_div2 BETWEEN comment_division_2.min AND comment_division_2.max
LEFT  JOIN comment_division_3 ON question.no              = comment_division_3.no__question AND examinee.point_div3 BETWEEN comment_division_3.min AND comment_division_3.max
LEFT  JOIN comment_division_4 ON question.no              = comment_division_4.no__question AND examinee.point_div4 BETWEEN comment_division_4.min AND comment_division_4.max
WHERE exam.cnt   = ?
AND   exam.class = ?

LEFT JOINで左外部結合を行っているので、参照値が下限〜上限に収まらない場合でもNULLが代入され、エラーにならない。