こんにちわ、PHPエンジニアのエンジニア婦人（@naho_osada）です。
PHPエンジニアとして9年～の経験があります。
「JOINの中でJOINする」―やると複雑になりがち。サブクエリを用いた、複雑なSQLを作らなければならないときの作り方手順を言語化しました。
ポイントは「SELECT文の結果を元にさらにJOINする」ことです。
結合数が多くなりがちですが、じっくり取り組めば大丈夫ですよ。
完成予定のSQL
今回、最終的に作りたいSQLはこれです。
SELECT
doc.document_id, doc.document_name,
stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section,
stf2.staff_name as name, stf2.department_name as department, stf2.section_name as section,
stf3.staff_name as name, stf3.department_name as department, stf3.section_name as section,
stf4.staff_name as name, stf4.department_name as department, stf4.section_name as section,
stf5.staff_name as name, stf5.department_name as department, stf5.section_name as section
FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf5 ON stf5.staff_id = trg.author_id_5;
このような、SQLの「JOINの中でJOINする、複雑なSQL」を作成するときの考え方の手順を紹介します。
※既にある複雑なSQLを修正するときの考え方はこちらです→「複雑なSQLを修正するときの考え方」
やりたいこととテーブルの構造
「documentテーブルから書類名称（doeument_name）とその作成者（staff_name）、部署（department_name / section_name）を取得して一覧表示する」
とします。
documentテーブルの「doc_target_id」はdoc_targetテーブルの「doc_target_id」と紐づきます。
これは「書類のIDとその作成者たちを紐づけている」とします。
doc_targetテーブルの「author_1～5」はstaffテーブルの「staff_id」と紐づきます。
その他、staffテーブルの「department」はdepartmentテーブルの「department_id」、「section」はsectionテーブルの「section_id」と紐づきます。
取得したいイメージはこんな感じです。
作成者は複数いるので、横に繋がっていきます。
各テーブルのデータ
各テーブルに入っているデータです。
documentテーブルは書類の基本情報を格納しています。
doc_targetテーブルは書類の作成者情報が入っています。author_id_5まであり、1レコードに5人までの作成者を登録できます。
staffテーブルは作成者となる人の情報が入っています。
departmentテーブルは部署情報が入っています。
sectionテーブルは課の情報が入っています。
お約束
今回は、テーブル構造には突っ込んではいけません。
それぞれのシステムに、それぞれの色々な事情があるんですよ。どうにもならないこともあるんです。
1：documentテーブルとdoc_target_tableの情報を取得する
「documentテーブルから書類名称（doeument_name）とその作成者（staff_name）、部署（department_name / section_name）を取得して一覧表示する」
ので、documentテーブルを基準としてdoc_target_tableをつなげてみます。
SELECT * FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
これを実行すると、以下の結果が取得できます。
2：staffテーブルを追加連結する
1の状態では著者情報（author_id_1など）が数値のままで、名前がわかりません。
1の結果を元に、staffの情報をJOINして、名前を取得します。まずは1人分。
SELECT * FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN staff as stf1 ON stf1.staff_id = trg.author_id_1
右にstaffの情報が出てきました。
これを5人分（author_id_1～5）まで連結すると、こうなります。
単純にLEFT JOINを5回です。
SELECT doc.document_id, doc.document_name, stf1.staff_name, stf2.staff_name, stf3.staff_name, stf4.staff_name, stf5.staff_name FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN staff as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN staff as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN staff as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN staff as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN staff as stf5 ON stf5.staff_id = trg.author_id_5;
ちょっと結果が横長になってくるので、表示項目を絞りました。
3：staffテーブルと部署テーブルをつなげてみる
ここまでで「書類名称と作成者」の情報は取得できました。しかしまだ「作成者の部署と課」がわかりません。
これを取得するには「各個人に対してさらにdepartmentテーブルとsectionテーブルを連結する」必要があります。
そこで、一旦「staffテーブルとdepartmentテーブルを連結」してみます。
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id;
部署名がついてきました。ここに課名も追加します。
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id;
INNER JOINで特に問題なく、人と部署、課名が取得できました。
4：全部まとめて取得する
最後に、2と3のSQLを同時に実行して、一つの表として取得します。
このとき、2でJOINして紐づける「staff_id」と「author_id_1～5」の中に、3のSQLを入れます。
3のSQLを実行した結果を元にしつつ、 「staff_id」と「author_id_1～5」 を紐づけるということです。
まずは1人分を連結してみます。
SELECT
doc.document_id, doc.document_name,
stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section
FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1;
LEFT JOINの中に3のSQL分が入りました。この結果はこうなります。
5人分連結するには、これと同じLEFT JOINを量産します。
as stf1のところは数字をかえないとエラーになります。
SELECT
doc.document_id, doc.document_name,
stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section,
stf2.staff_name as name, stf2.department_name as department, stf2.section_name as section,
stf3.staff_name as name, stf3.department_name as department, stf3.section_name as section,
stf4.staff_name as name, stf4.department_name as department, stf4.section_name as section,
stf5.staff_name as name, stf5.department_name as department, stf5.section_name as section
FROM document as doc
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN (
SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf5 ON stf5.staff_id = trg.author_id_5;
まとめ
JOINの中でさらにJOINする、複雑なSQLも順を追って考えていくとシンプルなSQL文がくっついたものだとわかります。
ポイントは「SELECT文の結果を元にさらにJOINする」ことです。
困ったときにはやりたいことを分解して、一つずつ結び付けていくと欲しい物が作りやすくなるかなと思います。
