複数テーブル検索で同idの部分は結合させたい

質問

初心者です。
ちょっと伝えずらいのですが、
下記のようなテーブル構造になっています。

■tableA
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 1 | test1 | aaaaaaaaaa |
| 2 | test2 | bbbbbbbbbbbb |
+----+-------------------+------------------------------------------+

■tableB
+----+-------------------+------------------------------------------+
| id | name | sex |
+----+-------------------+------------------------------------------+
| 1 | taro | men |
| 2 | yoko | women |
+----+-------------------+------------------------------------------+

■tableC
+----+-------------------+------------------------------------------+
| id | num | order_id |
+----+-------------------+------------------------------------------+
| 1 | 33 | 1 |
| 2 | 66 | 2 |
+----+-------------------+------------------------------------------+

■tableD
+-------------------+------------------------------------------+
| icons | order_id |
.+-------------------+------------------------------------------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 5 | 1 |
| 1 | 2 |
| 3 | 2 |
+-------------------+------------------------------------------+

tableA,B,Cのidを軸にデータを表示したいのですが、

---------------------------------------------------------
tableA.id , body , name , sex , num , order_id
FROM tableA , tableB , tableC
WHERE
tableA.id = tableB.id AND
tableA.id = tableC.id
----------------------------------------------------------

で出力できました。

で、tableDのiconsの内容も出力したいのですが、
---------------------------------------------------------
tableA.id , body , name , sex , num , order_id , icons
FROM tableA , tableB , tableC
WHERE
tableA.id = tableB.id AND
tableA.id = tableC.id AND
tableC.order_id = tableD.order_id
----------------------------------------------------------

でやると、2行しかデータがでないはずなのですが、
6行出力されてしまいます。

たぶん、tableDのデータの持ち方が、
ひとつのidに複数のiconsがある場合は、
行ごとにレコードを分けているためだと思いますが・・・
tableDの行分、6行でているのです。。

なので、期待する結果は、
ちゃんとidの数分だけ出力されて、
同order_idにiconsが複数ある場合は、
行ごとに分かれるのではなく、
ひとつのカラム？にカンマ区切りなどで
入れたいのですがどうしたらよいでしょうか？？

↓期待する結果
-----------------------------------------------------------------------
+----+-------------------+------------------------------------------+
| id | title | body | order_id | icons
+----+-------------------+------------------------------------------+
| 1 | test1 | aaaaaaaaaa | 1 |1,2,3,5
| 2 | test2 | bbbbbbbbbbbb | 2 | 1,3
+----+-------------------+------------------------------------------+
※カンマじゃなくてもいいです

わかりづらくて申し訳ありませんが、
ご教授お願いいたします。

MySQL 5.0.90-log
ちなみにec-cubeの高度な設定の
csvを吐き出す部分で使用しています。

回答No.1

yambejp · ベストアンサー

group_concatを使ってみてください

select tableA.id , body , name , sex , num , tableC.order_id , group_concat(icons order by icons) as icons
FROM tableA , tableB , tableC ,tableD
WHERE
tableA.id = tableB.id AND
tableA.id = tableC.id AND
tableC.order_id = tableD.order_id
group by tableA.id , body , name , sex , num , tableC.order_id

お礼

ありがとうございます！

実は、実際は、カラムがものすごい量があって、

group by tableA.id , body , name , sex , num , tableC.order_id

の部分は全部入れないといけないものなのでしょうか？？

ただだいぶいいところまで来ました！！

回答No.3

nora1962

解決しているみたいですが
create index idx_order_id on tableD ( order_id, icons );
で索引つくって
select
tableA.id , tableA.body , tableB.name , tableB.sex ,
tableC.num , tableC.order_id,
group_concat(tableD.icons order by icons)
FROM tableA inner join tableB
on tableA.id = tableB.id
inner join tableC
on tableA.id = tableC.id
inner join tableD
on tableC.order_id=tableD.order_id
group by tableD.order_id;

実行計画を比較してみてください。

お礼

ありがとうございます！

回答No.2

yambejp

ごめんなさい、手を抜きすぎた。
こんな感じでどうですか？

select tableA.id , body , name , sex , num , tableC.order_id ,icons
FROM tableA , tableB , tableC
, (SELECT order_id, group_concat(icons order by icons) as icons
FROM tableD
group by order_id) AS D
WHERE tableA.id = tableB.id
AND tableA.id = tableC.id
AND tableC.order_id = D.order_id

お礼

どちらもできるんですね！

ありがとうございます！！

補足

すいません、、、

: Unknown column 'order_id' in 'field list']

というエラーが出てしまいました。。。

ためしに、tableAをつけてみたんですが、
: Unknown column 'tableA.order_id' in 'field list']

となってしまいました。。。