Mysql concatenate parents and children as string-CodePudding

This is my database on mysql and I need extract concatenating all the parents and all children.

id     | name             | parent_id | has_children
100017 | Roupas Femininas | 0         | 1
100018 | Meias            | 100017    | 1
100419 | Outros           | 100118    | 0
100418 | Meia calça       | 100118    | 0
100417 | Meias            | 100118    | 0
100117 | Tecidos          | 100017    | 1
100416 | Outros           | 100117    | 0
100413 | Lona             | 100117    | 0
100412 | Jeans            | 100117    | 0

Example as I need return:

Roupas Femininas > Meias > Outros
Roupas Femininas > Meias > Meia calça
Roupas Femininas > Meias > Meias
Roupas Femininas > Tecidos > Outros
Roupas Femininas > Tecidos > Lona
Roupas Femininas > Tecidos > Jeans

How can I extract this data from my database to get that example as return?

I've been looking everywhere, and reading this Hierarchical Data in MySQL, but I didn't find the result I was looking for.

I got closer to the expected result, but I couldn't identify the problem.

SELECT CONCAT( REPEAT(' > ', COUNT(parent.nome_shopee) - 1), node.nome_shopee) AS name
FROM categoria_shopee_lista AS node,
    categoria_shopee_lista AS parent
WHERE node.categoria_pai BETWEEN parent.categoria_pai AND parent.nome_shopee
GROUP BY node.nome_shopee
ORDER BY node.id;

Result:

 >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >Roupas Femininas
 >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >  >Outros
 etc...

Mysql online

Is this the correct way?

thanks

CodePudding user response：

WITH RECURSIVE
cte AS (
  SELECT *, name AS path
  FROM test
  WHERE NOT parent_id
  UNION ALL
  SELECT test.*, CONCAT_WS(' > ', cte.path, test.name)
  FROM cte
  JOIN test ON cte.id = test.parent_id
)
SELECT path
FROM cte
WHERE NOT has_children;

path
Roupas Femininas > Meias > Outros
Roupas Femininas > Meias > Meia calça
Roupas Femininas > Meias > Meias
Roupas Femininas > Tecidos > Outros
Roupas Femininas > Tecidos > Lona
Roupas Femininas > Tecidos > Jeans

fiddle

CodePudding user response：

If you know there are max 3 levels (no indefinite depth of levels where recursion should be used) then you do not need chained query just this (just chain your tables three times over next.parent_id=prev.id principle):

SELECT
  CONCAT(l1.name,
  IF(l2.name IS NOT NULL,CONCAT(' > ',l2.name,''),
  IF(l3.name IS NOT NULL,CONCAT(' > ',l3.name,''))
FROM categoria_shopee_lista l1
LEFT JOIN categoria_shopee_lista l2 ON l2.parent_id=l1.id
LEFT JOIN categoria_shopee_lista l3 ON l3.parent_id=l2.id
ORDER BY l1.name,l2.name,l3.name