MYSQL 1対複数のLEFT JOINを1行で表示できるようにする。

 webプログラムを組む際にはデータベースとの連結は必須です。
かのワードプレスやEC-CUBEといったメジャーなプログラムはすべてデータベースと連結して豊富な機能を実現しています。

私も請謁ながらwebプログラム開発をさせていただくことがしばしばございます。その際にMYSQLと連結させて開発しています。

今回はちょっとしたことですが、役立ったテクニックがありましたので、忘備録として記録いたします。

今回やりたかったこと

データベースには異なったテーブル同士を「結合」して参照する方法があります。マイクロソフトアクセスでいうところの「クエリ」です。

結合方法にはいろいろあるのですが、今回は「LEFT JOIN」で1対複数のデータを結合します。

例えば1対10で結合したとすると、SELECTで最低10行のデータが読み込みされます。

今回これを1行で読み込みしたいと思いました。

早速ググって調べました。

たいていのことはググって調べると出てきます。
今回もちゃんと出てきました。

それによると「 」関数を使用するとできるとありました。
CONCATってエクセルにもある関数なのでイメージが付きます。
文字結合です。

ということで、MYSQLで実験してみました。

テストテーブルとデータは以下の通りです。

上位のAテーブルと、下位のBテーブルを準備ました。
それぞれ中身が入っています。

Bテーブルの中身はAテーブルのbase01と結合する形です。

通常のLEFT JOINで読み込みするとこうなります。

LEFT JOINでセレクトすると、以下のように複数行にわたって読み込みされます。

■SQL分


Bテーブルが3件あるので、結合して読み込むと全部で4行になります。
これはこれでその通りです。

今回base01 に結合しているsub01~sub03の3件を1件にまとめます。

GROUP_CONCAT を使って、テーブルBを一つにまとめます。

ではSQL文に GROUP_CONCAT 関数を追加してまとめて読み込みを実行してみます。

■SQL文

FROMの前にGROUP_CONCATを追加します。


予定通り3行になっていたデータが1行にまとまりました。
ってあれ、base02データが読み込みされていません。

LEFT JOINにしているのは、結合データがなくても全てのデータを読み込みしたいのにこれでは具合が悪いです。

何がいけないのでしょうか?

原因は文法不足

GROUP_CONCATを使う場合は、GROUP BY 文が必要です。
それが抜けていたため、base02が呼び出しされなかったのです。
そこで、GROUP BY文をつけて呼び出しします。

■SQL文


GROUP BY 文を付け足すことでちゃんと読み込みされるようになりました。

ソートして読み込みしたい場合はORDER BY文を付け足します。

実用的なことを言えば、データのソートが必要になります。
そこで、ORDER BY文を付け足します。

■SQL文

ソートを付け足せばOKです。


あれ、文法エラーになりました。
なぜでしょうか?

原因はORDER BYとGROUP BYの表記順番

文法エラーになるのは、ORDER BY文とGROPU BY文の表記順番が間違っているためです。

この2つの文には以外にも表記順番があります。
それがこちらです。

■SQL文

なんだかんだと言いつつ、GROUP BYを先にして、ORDER BYを後にします。


うまく呼び出しができました。

SQL文は組み合わせを考えて効率よく

今回は1対複数結合を1行にまとめるという目的でGROUP_CONCATを使用しました。そもそも結合データをキーワード検索をする際に、テーブルAだけの情報を出力したいと考えたのですが、通常LEFT JOINだと同じデータが複数表示されるため、試行錯誤しました。

あとから、サブクエリで検索したほうが早くないか?と気づきサブクエリに変えました。

ただ、サブクエリはケースによっては重くなることもあるため、必ずしもサブクエリが良いとも言えません。

そういった意味では、GROUP_CONCATは利便性の高い関数であることを体験できました。

今回のレポートは以上です。
読んでいただいてありがとうございました。


ホームページに関するお悩み事やご相談事がございましたら私どもまでご連絡ください。 鋭意ご対応申し上げます。
ホームページのご提案もさせていただいております