[MySQL]1つのキーに対して結果が複数ある場合にカンマ区切りで結果をまとめる

MySQL

ユーザー情報テーブルとカテゴリー登録テーブルの複数のテーブルがあるとします。
ユーザーIDに対して登録されているカテゴリーの情報を抽出したい時に、単純にSQL文を書いてしまうとカテゴリーが複数登録されているユーザーに対して結果が複数返ってくることになります。

また、カテゴリー情報が存在しないユーザーIDは結果として返ってきません。
そこで、結果をまとめて1行にしてしまう方法と存在しないユーザーも漏れなく結果として返す方法をご紹介します。

下記のテーブルを元にします

データ

【ユーザー情報テーブル:T1】

ユーザーID ユーザー名
1 たぬき
2 きつね
3 ねこ
4 こじか
5 かに
6 にくきゅう
7 うさぎ
8 いぬ

【カテゴリー登録テーブル:T2】

ユーザーID 項目名
1 バナナ
1 りんご
1 イチゴ
2 りんご
2 みかん
3 バナナ
3 イチゴ
4 みかん
5 すいか
6 バナナ
6 りんご
6 イチゴ
7 みかん

ユーザーIDに紐付けられた項目名を抽出しようと単純にSQL文を書くと

となり、結果はT2テーブルと同じ状態になります。

T1.ユーザーID T2.項目名
1 バナナ
1 りんご
1 イチゴ
2 りんご
2 みかん
3 バナナ
3 イチゴ
4 みかん
5 すいか
6 バナナ
6 りんご
6 イチゴ
7 みかん

このSQL文にLIMITを付けてしまうと、

T1.ユーザーID T2.項目名
1 バナナ
1 りんご

本来は2人分のユーザーに対するカテゴリー情報が取り出したいのに、1人分の上、ユーザーIDが1に対して3つのカテゴリー情報があるのに2つしか返ってこないという結果になります。

そこで、項目名をまとめることで本来の目的にそった結果を取得してみます。

結果をまとめる

T1.ユーザーID 項目名まとめ
1 バナナ,りんご,イチゴ
2 りんご,みかん
3 バナナ,イチゴ
4 みかん
5 すいか
6 バナナ,りんご,イチゴ
7 みかん

GROUP_CONCATを使うことで項目名がまとまりました。
これでLIMITを使ってもきちんと狙った人数分の結果が返ってくることになります。
しかもカンマ区切りで結果にセットされるので結果を分けて表示したい場合はPHPならばexplodeを使うなどしてしまえば個別の値として扱えます。

しかし、ユーザーIDが8の「いぬ」さんはカテゴリー情報が登録されていないので結果に入っていません。
全ユーザーを対象として抽出した場合に抜けが発生していますのです。
これは、ユーザー情報テーブル(T1)とカテゴリー登録テーブル(T2)を単純に結びつけて合致するデータのみを対象としてしまったのが原因です。

そこで、カテゴリー情報の登録がなくても結果が返ってくるように頑張ってみます。

抜けが無いようにした上で結果をまとめる

ユーザー情報テーブル(T1)を軸としてカテゴリー登録テーブル(T2)を結び付けます。その上でGROUP_CONCATを使って結果をまとめます。

結果は

T1.ユーザーID 項目名まとめ
1 バナナ,りんご,イチゴ
2 りんご,みかん
3 バナナ,イチゴ
4 みかん
5 すいか
6 バナナ,りんご,イチゴ
7 みかん
8 NULL

ユーザーIDが8に対して項目名まとめにNULLが返ってきました。

LEFT OUTER JOIN でT1にあるユーザーIDに対してT2の項目名を当て込みますので、ユーザーIDが合致した場合には項目名が当てはまり、カテゴリー登録がなければNULLとして残るようになります。

カンマ区切りになった結果をPHPで扱いたい場合はexplodeを使えば簡単です。
explodeを利用する記事はコチラを参考にしてみてください

[PHP]文字列にまとめた値を配列に変換する