Mysql Mariadb recursive query

Question

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

Answer 1

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 ids 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