Select tree path in MySQL table

Question

I have a MySQL table like this:

| CategoryId |          Name | CategoryParentId |
|------------|---------------|------------------|
|          0 |  Tech Support |           (null) |
|          1 | Configuration |                0 |
|          2 |     Questions |                1 |
|          3 |         Sales |           (null) |
|          4 |     Questions |                3 |
|          5 |         Other |           (null) |

This is the output I desire when a query the ID 2 (for example):

Tech Support/Configuration/Questions

How do I do this without having to do multiple joins?

Fiddle

EDIT: Not sure if is the best way to do this, but I solved by creating a function:

DELIMITER $$

CREATE FUNCTION get_full_tree (CategoryId int) RETURNS VARCHAR(200)

BEGIN
SET @CategoryParentId = (SELECT CategoryParentId FROM category c WHERE c.CategoryId = CategoryId);
SET @Tree = (SELECT Name FROM category c WHERE c.CategoryId = CategoryId);
WHILE (@CategoryParentId IS NOT NULL) DO
    SET @ParentName = (SELECT Name FROM category c WHERE c.CategoryId = @CategoryParentId);
    SET @Tree = CONCAT(@ParentName, '/', @Tree);
    SET @CategoryParentId = (SELECT CategoryParentId FROM category c WHERE c.CategoryId = @CategoryParentId);
END WHILE;
RETURN @Tree;
END $$
DELIMITER ;

I can now do this query:

SELECT CategoryId, get_full_tree(CategoryId) FROM category

Not sure if that can be done in one line. You can do SELECT NAME FROM MYTABLE WHERE CATEGORYID <= p_CategoryId; and write results into a temp table and then use WHILE loop on the temp table to concat names. Or you can use a cursor to concat names. — Allen King
– Allen King, Commented May 28, 2017 at 22:42

Trying · Accepted Answer · 2017-05-28 23:38:37Z

You could create a new table, lets name it as hierarchy (could be a better name) where we would store all the ancestry of a category.

CREATE TABLE `hierarchy` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `parent` int(11) NOT NULL,
  `child` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

For example in this case for Questions i.e. ID->2 we will have the below entries:

id  parent     child
====================            
6     0         2
7     1         2
8     2         2

For the whole example the content of the table would be:

id       parent     child
===========================
1           0           0
2           3           3
3           5           5
4           0           1
5           1           1
6           0           2
7           1           2
8           2           2
9           3           4
10          4           4

Now whenever you want to retrieve the whole ancestry of node execute the below query:

select name from category where id in (select parent from hierarchy where child = 2 order by id ASC)

The above query will return all the ancestry names for the Questions (ID->2) i.e.

name
==================
Tech Support
Configuration
Questions

For completeness shake below is the content for category table

id             Name
============================
0             Tech Support
1             Configuration
2             Questions
3             Sales
4             Questions
5             Other

N.B. This is just an idea i am sure you can definitely build more elegant solution on top of it.

Alexey Alexeyev · Accepted Answer · 2020-01-07 14:51:47Z

1

If you are using MySQL 8 or above you can use Common Table Expressions for recursive queries. Query would be the following

WITH RECURSIVE CategoryPath (CategoryId, Name, path) AS
(
  SELECT CategoryId, Name, Name as path
    FROM category
    WHERE CategoryParentId IS NULL
  UNION ALL
  SELECT c.CategoryId, c.Name, CONCAT(cp.path, ' / ', c.Name)
    FROM CategoryPath AS cp JOIN category AS c
      ON cp.CategoryId = c.CategoryParentId 
)
SELECT * FROM CategoryPath ORDER BY path;

answered Jan 7, 2020 at 14:51

Alexey Alexeyev

211 bronze badge

Collectives™ on Stack Overflow

Select tree path in MySQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related