How to add group_concat function with another group

I am trying to add all attributes from the child_att column into the parent_att column field with the relevant sku in front.

So far my table and query is this.

SELECT 
    prod.sku, prod.type, prod.parent, child_att
        FROM prod
            LEFT JOIN (
SELECT 
    att.sku, 
    GROUP_CONCAT (att.name, '=', att.value) AS child_att
        FROM att
            GROUP BY att.sku
  ) att ON att.sku = prod.sku
;

| sku  | type   | parent | child_att                |
| ---- | ------ | ------ | ------------------------ |
| sve1 | child  | sve4   | Size=Large,Colour=Red    |
| sve2 | child  | sve4   | Colour=Blue,Size=Medium  |
| sve3 | child  | sve4   | Size=Small,Colour=Orange |
| sve4 | parent | sve4   |                          |

my desired result is this

| sku  | type   | parent | child_att                | parent_att |
| ---- | ------ | ------ | ------------------------ | ---------- |
| sve1 | child  | sve4   | Size=Large,Colour=Red    |            |
| sve2 | child  | sve4   | Colour=Blue,Size=Medium  |            |
| sve3 | child  | sve4   | Size=Small,Colour=Orange |            |
| sve4 | parent | sve4   |                          | sku=sve1,Size=Large,Colour=Red,sku=sve2,Colour=Blue,Size=Medium,sku=sve3,Size=Small,Colour=Orange |

I have managed to get a result using the following query but I am struggling to add both querys together.

SELECT GROUP_CONCAT(attributes SEPARATOR '|') AS parent_att
FROM (
      SELECT CONCAT('sku=', prod.sku, ',', GROUP_CONCAT(att.name, '=', att.value)) attributes
        FROM prod
          LEFT JOIN att ON att.sku = prod.sku
            GROUP BY prod.sku
  ) AS tbl
;

https://www.db-fiddle.com/f/pbPQgtHNtq8ovK232XZdn9/4

CodePudding user response：

Assuming your PARENT-CHILD relationship has only one level, you can do:

SELECT 
  p.sku, 
  p.type, 
  p.parent, 
  GROUP_CONCAT (a.name, "=", a.value) AS child_att,
  NULL AS parent_att
FROM prod p
  LEFT JOIN att a ON a.sku = p.sku
WHERE p.type='child'
GROUP BY p.sku, p.type, p.parent
UNION
select 
  sku, 
  type, 
  parent, 
  null AS child_att, 
  GROUP_CONCAT(parent_att) AS parent_att
FROM (
    SELECT 
      p.sku, 
      p.type, 
      p.parent, 
      CONCAT('sku=', pc.sku, ';',GROUP_CONCAT (ac.name, "=", ac.value)) AS parent_att
    FROM prod p
      LEFT JOIN prod pc ON pc.parent = p.sku
      LEFT JOIN att ac ON ac.sku = pc.sku
    WHERE p.type='parent'
    GROUP BY p.sku, pc.sku, p.type, p.parent
) AS q
GROUP BY sku,type,parent

See: https://www.db-fiddle.com/f/pbPQgtHNtq8ovK232XZdn9/6

CodePudding user response：

We just need to add a parent_att column using CASE in the SELECT list. The following is written and tested in workbench. Try it:

SELECT 
    prod.sku, prod.type, prod.parent, child_att,
    case when child_att is not null then ''
    else (
        select group_concat('sku=',sku,',',attr) 
        from (select sku,GROUP_CONCAT(name , '=', value order by name desc) as attr
            FROM att
            group by sku
            ) t 
        ) end as parent_att 
FROM prod
    LEFT JOIN (
SELECT 
    att.sku, 
    GROUP_CONCAT(att.name, '=', att.value) AS child_att
        FROM att
            GROUP BY att.sku
  ) att ON att.sku = prod.sku
;
-- result set:
# sku, type, parent, child_att, parent_att
sve1, child, sve4, colour=red,size=large, 
sve2, child, sve4, colour=blue,size=medium, 
sve3, child, sve4, colour=orange,size=small, 
sve4, parent, sve4, , sku=sve1,size=large,colour=red,sku=sve2,size=medium,colour=blue,sku=sve3,size=small,colour=orange