SQLのSELECTで外部テーブルを非正規化して表示

Question

以下のようなスキーマにおいて、

CREATE TABLE `orders` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `order_products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `order_id` bigint(20) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_orders` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`)
);

以下のようなデータが入っているときに、

INSERT INTO orders (id, code) VALUES(1, 'aaa');
INSERT INTO order_products (id, order_id, name) VALUES(1, 1, 'product a');
INSERT INTO order_products (id, order_id, name) VALUES(2, 1, 'product b');
INSERT INTO order_products (id, order_id, name) VALUES(3, 1, 'product c');

> select * from orders;
+----+------+
| id | code |
+----+------+
|  1 | aaa  |
+----+------+

> select * from order_products;
+----+----------+-----------+
| id | order_id | name      |
+----+----------+-----------+
|  1 |        1 | product a |
|  2 |        1 | product b |
|  3 |        1 | product c |
+----+----------+-----------+

以下のように子テーブルを非正規化して、親のレコード1行に対して子供のとあるカラムを非正規化して抽出することは可能でしょうか？

+----+------+---------------------------------+
| id | code | denormalization                 |
+----+------+---------------------------------+
|  1 | aaa  | product a, product b, product c |
+----+------+---------------------------------+

変な要求かもしれませんが、親のレコード単位で集計しつつ、子供の値も予備情報として出力したいです。

Answer 1

ordersのcodeでユニークが保証されているかどうか分からないので、idは最小値。
order_productsには同じ(order_id, name)の組合せがあるでしょうから、DISTINCTで纏める。

select min(orders.id) as id 
     , orders.code 
     , group_concat(distinct order_products.name order by order_products.name) as denormalization 
from orders 
     left join order_products 
     on orders.id = order_products.order_id
group by orders.code

Comments

[matsubokkuri]

group_concat知らなかったです！便利！

```
> select min(orders.id) as id
-> , orders.code
-> , group_concat(distinct order_products.name order by order_products.name) as denormalization
-> from orders
-> left join order_products
-> on orders.id = order_products.order_id
-> group by orders.id
-> ;
+------+------+-------------------------------+
| id | code | denormalization |
+------+------+-------------------------------+
| 1 | aaa | product a,product b,product c |
+------+------+-------------------------------+
1 row in set (0.00 sec)
```

[sazi]

Group By項目が間違っていましたので訂正しました。

Answer 2

SELECT ... JOIN ... で複数のテーブルからSELECTするのも含めて、以下のURLをよーく見ていただくとして、MySQLはGROUP_CONCAT で横に並べます。

Answer 3

select 
    O.id, 
    O.code, 
    group_concat(P.name) as denormalization 
from orders O
left join order_products P on O.id = P.order_id
group by O.id