By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `tbl` (
`id` int(12) NOT NULL,
`name` varchar(50) NOT NULL,
`parentid` int(12) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `tbl` (`id`, `name`, `parentid`) VALUES
(1, 'categ 1', 0),
(2, 'xcateg 1.1', 1),
(3, 'acateg 1.2', 1),
(4, 'categ 1.2.1', 3),
(5, 'categ 2', 0),
(6, 'categ 2.1', 5);
Records: 6 Duplicates: 0 Warnings: 0
WITH RECURSIVE tree_search (id, name, lvl, parentid) AS (
SELECT id, name, 0, parentid
FROM tbl
WHERE parentid = 0
UNION ALL
SELECT t.id, t.name,
ts.lvl + 1, ts.id
FROM tbl AS t
JOIN tree_search AS ts ON t.parentid = ts.id
)
SELECT t.*, tb.`name` FROM tree_search t inner Join tbl tb ON t.parentid = tb.id
ORDER BY parentid, lvl, t.name;
| id | name | lvl | parentid | name |
|---|---|---|---|---|
| 3 | acateg 1.2 | 1 | 1 | categ 1 |
| 2 | xcateg 1.1 | 1 | 1 | categ 1 |
| 4 | categ 1.2.1 | 2 | 3 | acateg 1.2 |
| 6 | categ 2.1 | 1 | 5 | categ 2 |