I have the following table :
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);
and a recursive query on it :
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 * FROM tree_search ORDER BY parentid, lvl, name;
which works and prints the following result :
id | name | lvl | parentid | ----+--------------+-------+----------+ 1 | categ 1 | 0 | 0 | 5 | categ 2 | 0 | 0 | 3 | acateg 1.2 | 1 | 1 | 2 | xcateg 1.1 | 1 | 1 | 4 | categ 1.2.1 | 2 | 3 | 6 | categ 2.1 | 1 | 5 |
I would have liked to add an extra column to the result, namely “parent_name”. How do I do this?
Edited to add: https://stackoverflow.com/a/22376973/2027239 That was the answer on an older question which I used to build my query
Advertisement
Answer
Use an inner join of tbl
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);â â
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
db<>fiddle here
Or use left join
to get all id
s even those which have no parent.
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);â â
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 LEFT Join tbl tb ON t.parentid = tb.id ORDER BY parentid, lvl, t.name;id | name | lvl | parentid | name -: | :---------- | --: | -------: | :--------- 1 | categ 1 | 0 | 0 | null 5 | categ 2 | 0 | 0 | null 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
db<>fiddle here