Is it possible to do a group_concat inside another group_concat?

60
January 13, 2019, at 09:00 AM

I'm building a webshop that sells shoes. I'm trying to write a command that will show the quantity of each size for 1 specific sneaker.

This is my ER-diagram

Link to my ER-Diagram can be seen here: imgur.com/a/QHpid2p

I want to add the quantity (qty) to all the different sizes but everything I try doesn't seem to work.

SELECT sn.sneaker_id, sn.sneaker_name,
       (CASE WHEN sn.gender = 0 THEN 'Women'
             WHEN sn.gender = 1 THEN 'Men'
        END) as gender,
       sn.description, sn.price,
       GROUP_CONCAT(si.size),
       GROUP_CONCAT(DISTINCT(i.path)),
       bn.brand_name
FROM sneakers sn JOIN
     sizes si
     ON si.sneaker_fk = sn.sneaker_id JOIN
     images i
     ON i.sneaker_fk = sn.sneaker_id JOIN
     brand_names bn
     ON bn.brand_name_id = sn.brand_name_fk
WHERE sn.sneaker_id = 1

This is my actual result:

sneaker_id : 1
sneaker_name : Air Max 97 Silver Bullet
gender : Men
description : anfaslknsaf
price : 1500
GROUP_CONCAT(sizes.size) : 41.0,43.0,44.0,45.5,46.0
GROUP_CONCAT(DISTINCT(images.path)): img
brand_name : nike

My desired output would be something like:

sneaker_id : 1
sneaker_name : Air Max 97 Silver Bullet
gender : Men
description : anfaslknsaf
price : 1500
GROUP_CONCAT(sizes.size) : [41.0, 10],[43.0, 5],[44.0, 8],[45.5, 7],[46.0,8]
GROUP_CONCAT(DISTINCT(images.path)): img
brand_name : nike
Answer 1

It is a good practice to aggregate by all the unaggregated columns in a GROUP BY query.

GROUP_CONCAT() accepts multiple arguments and concatenates them together. So, you can do:

SELECT sn.sneaker_id, sn.sneaker_name,
       (CASE WHEN sn.gender = 0 THEN 'Women'
             WHEN sn.gender = 1 THEN 'Men'
        END) as gender,
       sn.description, sn.price,
       GROUP_CONCAT('[', si.size, ', ', si.qty, ']'),
       GROUP_CONCAT(DISTINCT(i.path)),
       bn.brand_name
FROM sneakers sn JOIN
     sizes si
     ON si.sneaker_fk = sn.sneaker_id JOIN
     images i
     ON i.sneaker_fk = sn.sneaker_id JOIN
     brand_names bn
     ON bn.brand_name_id = sn.brand_name_fk
WHERE sn.sneaker_id = 1
GROUP BY sn.sneaker_id, sn.sneaker_name, gender, sn.description, sn.price, bn.brand_name;

Note that you can remove the WHERE clause and this will work for all sneaker ids.

Answer 2

You need to concat both columns manually:

GROUP_CONCAT(CONCAT('[', sizes.size, ', ', sizes.qty, ']'))
READ ALSO
MySql incremental backup of only added lines

MySql incremental backup of only added lines

How can I create incremental backups of a MySql database that only includes data that is added and ignores data that has been removed?

26
How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

How to switch a SQL 5.6 database to a SQL 5.5 database [on hold]

I've to launch a website that I develop on my side with a SQL version 56

42
SUM quantity of products that belong to the same group (group_id)

SUM quantity of products that belong to the same group (group_id)

I have a product catalog, each product have 2 quantities (products_quantity and products_incoming_quantity), some products are grouped together under group_id

57
I can not recover data, even by dumping the Wordpress database

I can not recover data, even by dumping the Wordpress database

I recently made a backup of my Wordpress cms database, but now when I restore using the same sql commands in mysql, of success in the dump, however the data of the backup is not recovered, for example the posts, I have more than 100 and did not recover...

30