MariaDB query for parent and child category

Question

I have this MariaDB table "categories":

id  category        parent_id
1   electronics     0
2   fashion         0
3   TV              1
4   mobile          1
5   Sony            3
6   power bank      1
7   iphone          4

and I want result using MYSQL Query

1 electronics
3 electronics >> TV
5 electronics >> TV >> Sony
4 electronics >> Mobile
7 electronics >> Mobile >> iphone
6 electronics >> Power bank
2 fashion

when am run this SELECT VERSION(); output like 10.4.17-MariaDB — Durairaj, Mar 16 '21 at 11:56
I think this will help https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query — habib, Mar 16 '21 at 12:50
I edited to make it clear you use MariaDB, not MySQL. These two products have diverged enough that we should think of them as different databases with different features. — Bill Karwin, Mar 16 '21 at 15:20

score 0 · Answer 1 · answered Mar 16 '21 at 12:33

WITH RECURSIVE
cte AS ( SELECT id, category, parent_id, category path
         FROM categories
         WHERE parent_id = 0
       UNION ALL
         SELECT c.id, c.category, c.parent_id, CONCAT(cte.path, ' > ', c.category)
         FROM categories c
         JOIN cte ON cte.id = c.parent_id )
SELECT * FROM cte;

https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=d9d59ace66a91b6b95c14e31cd1f6d1b

MariaDB query for parent and child category

1 Answers1