Grouping data in the same query with many joins

Question

i have a few tables:

products: id (int), name (varchar)
colors: id (int), name (varchar)
sizes: id (int), name (varchar)
products_colors: color_id (int), product_id (int)
products_sizes: size_id (int), product_id (int)

I want to generate only one query that returns all the information (instead of 3 queries, 1 for products, 1 for colors and one for sizes) I found that i can do something "decent" that using GROUP_CONCAT but the query is returning duplicate data, not sure if that is happening because the GROUP_CONCAT or the JOINS

SELECT products.id, products.name, CONCAT("[", GROUP_CONCAT("'", colors.name, "'"), "]")     colors, CONCAT("[", GROUP_CONCAT("'", sizes.name, "'"), "]") sizes
FROM products 
LEFT JOIN products_sizes ON products_sizes.product_id = products.id
LEFT JOIN sizes ON sizes.id = products_sizes.size_id
LEFT JOIN products_colors ON products_colors.product_id = products.id
LEFT JOIN colors ON colors.id = products_colors.color_id
GROUP BY products.id

Please note that i´m using CONCAT to format the grouped data as JSON If there is a match in products_colors AND products_sizes the data is duplicated in the GROUP_CONCAT

I need to return all the products no matter if there are colors or sizes available

Answer 1

Try adding distinct to the group_concat function:

SELECT products.id,
       products.name,
       case when colors.name is null then '[]' else
       CONCAT("[", GROUP_CONCAT(distinct "'", colors.name, "'"), "]") end as colors,
       case when sizes.name is null then '[]' else
       CONCAT("[", GROUP_CONCAT(distinct "'", sizes.name, "'"), "]") end as sizes
  FROM products
  LEFT JOIN products_sizes
    ON products_sizes.product_id = products.id
  LEFT JOIN sizes
    ON sizes.id = products_sizes.size_id
  LEFT JOIN products_colors
    ON products_colors.product_id = products.id
  LEFT JOIN colors
    ON colors.id = products_colors.color_id
 GROUP BY products.id, products.name