[MySQL] 1対多で紐づくテーブルが複数あった場合の「group_concat」

今回は1対多で紐づくテーブルが複数あった場合の「group_concat」の使用方法についてです。

以下のように3つのテーブルがあります。
各テーブルはclassテーブルのidをkeyとして紐付けられています。

classテーブル

studentテーブル

itemテーブル

まずは各クラスに属している生徒名を調べるために
「group_concat」を使用して以下のSQLを作成しました。

select
  a.id
  , a.classname
  , group_concat(b.name) 
from
  class a 
  left join students b 
    on a.id = b.classid 
group by
  a.id;

上記の実行結果：

これで各クラスに属する生徒がわかりました。

次に各クラスが所有する備品を調べるために
以下のSQLを作成しました。
こちらも「group_concat」を使用しています。

select
  a.id
  , a.classname
  , group_concat(b.name) 
from
  class a 
  left join items b 
    on a.id = b.classid 
group by
  a.id;

上記の実行結果：

これで各クラスが所有する備品がわかりました。

それでは、各クラスに属する生徒と各クラスが所有する備品を
同時に調べたい場合、どうすればいいでしょうか。

単純に上記の2つのSQLをミックスしたらどうなるか試してみました。

select
  a.id
  , a.classname
  , group_concat(c.name)
  , group_concat(b.name) 
from
  class a 
  left join items b 
    on a.id = b.classid 
  left join students c 
    on a.id = c.classid 
group by
  a.id;

上記の実行結果：

出力結果に同じ名称が重複して表示されてしまいました。

重複箇所を一意にするには「distinct」を使用すれば改善できるかもしれません。
上記のSQLに「distinct」を追加して出力してみました。

select
  a.id
  , a.classname
  , group_concat(distinct c.name)
  , group_concat(distinct b.name) 
from
  class a 
  left join items b 
    on a.id = b.classid 
  left join students c 
    on a.id = c.classid 
group by
  a.id;

上記の実行結果

うまくいったようです。

次にdistinctを使用しないで実現する方法はないかを検討しました。

サブクエリを使って出力させた場合はどうでしょうか。

以下、試してみました。

select
  a.id
  , a.classname
  , group_concat(distinct b.name) 
from
  class a 
  left join students b 
    on a.id = b.classid 
group by
  a.id;

上記のSQLを以下のようにサブクエリを使用するSQLに
変形しました。

select
  a.id
  , a.classname
  , ( 
    select
      group_concat(name) 
    from
      students 
    where
      classid = a.id
  ) studentname 
from
  class a;

上記の実行結果：

変形前と同じ出力結果で出力できることがわかりました。

次に、サブクエリを使った方法で、備品を確認するための
SQLを追加してみました。

select
  a.id
  , a.classname
  , ( 
    select
      group_concat(name) 
    from
      students 
    where
      classid = a.id
  ) studentname
  , ( 
    select
      group_concat(name) 
    from
      items 
    where
      classid = a.id
  ) itemname 
from
  class a;

上記の実行結果：

この方法で結果が重複することなく正しい結果が出力できました。
サブクエリを使って対応することも可能です。

以上、1対多で紐づくTableが複数あった場合の「group_concat」
の使用方法について紹介いたしました。

木曜日担当: nishida