Mysql - grouping 1 to many join

Question

Using the following tables:

products
product_id  |  name
1           |  Jeans
2           |  t-shirt

options
option_id   | product_id | option_name |  option_value
1           | 1          | size        |  32
2           | 1          | colour      |  blue
3           | 1          | price       |  100
4           | 2          | colour      |  red

I would like to run a query for a product and get back a single row containing the product data combined with the options for that product. The issue is the 1 to many relationship between the products and the options.

Is this possible in anyway maybe grouping them or having multiple columns in the result for each option?

Use `GROUP_CONCAT` to concatenate all the options for a product ID. — Barmar, May 17 '16 at 20:55
What are your expected results? You can group them with `group_concat`... — sgeddes, May 17 '16 at 20:56
Possible duplicate of [MySQL - Rows to Columns](http://stackoverflow.com/questions/1241178/mysql-rows-to-columns) — Matt Raines, May 17 '16 at 23:40

score 0 · Answer 1 · answered May 17 '16 at 20:58

Use GROUP_CONCAT.

SELECT p.product_id, p.name, GROUP_CONCAT(CONCAT(o.option_name, ' ', o.option_value)) AS options
FROM products AS p
LEFT JOIN options AS o ON p.product_id = o.product_id
GROUP BY p.product_id

I use LEFT JOIN so that products with no options will still be listed (with an empty options column). Use INNER JOIN if you don't want to see those products in the results.

Mysql - grouping 1 to many join

1 Answers1