By Tarun Parswani


2013-11-26 11:22:57 8 Comments

I have a MySQL table which is as follows:

id | name        | parent_id
19 | category1   | 0
20 | category2   | 19
21 | category3   | 20
22 | category4   | 21
......

Now, I want to have a single MySQL query to which I simply supply the id [for instance say 'id = 19'] then I should get all its child ids [i.e. result should have ids '20,21,22'].... Also, the hierarchy of the children is not known it can vary....

Also, I already have the solution using the for loop..... Let me know how to achieve the same using a single MySQL query if possible.

15 comments

@Dheerendra Kulkarni 2015-02-07 05:03:14

Did the same thing for another quetion here

Mysql select recursive get all child with multiple level

The query will be :

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
  SELECT @pv:=(
    SELECT GROUP_CONCAT(id SEPARATOR ',')
    FROM table WHERE parent_id IN (@pv)
  ) AS lv FROM table 
  JOIN
  (SELECT @pv:=1)tmp
  WHERE parent_id IN (@pv)
) a;

@Rahul 2015-06-12 01:34:08

How can we do this? SELECT idFolder, (SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM ( SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder WHERE idFolderParent IN (@pv)) AS lv FROM Folder JOIN (SELECT @pv:= F1.idFolder )tmp WHERE idFolderParent IN (@pv)) a) from folder F1 where id > 10; I can't refer F1.idFolder for @pv

@Damodaran 2013-11-26 11:36:05

From the blog Managing Hierarchical Data in MySQL

Table structure

+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+

Query:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

Output

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table. Read more

Refer the blog for more details.

EDIT:

select @pv:=category_id as category_id, name, parent from category
join
(select @pv:=19)tmp
where [email protected]

Output:

category_id name    parent
19  category1   0
20  category2   19
21  category3   20
22  category4   21

Reference: How to do the Recursive SELECT query in Mysql?

@Damodaran 2013-11-26 11:41:10

That blog is a nice tutorial for Hierarchical Data in MySQL. I do't know how much it will be useful in your case. Try it.. all the best :)

@Jonathan Leffler 2013-11-26 11:47:42

That's fine as long as there are no more than 4 levels at most in the hierarchy. If there are N levels, you have to know that to create the query properly.

@Tarun Parswani 2013-11-26 11:48:41

@Damodaran, Thanks for your reply... What I needed is a condition where the number of childs are not known... and in the blog that is using a inner join concept in that the hierarchy is required to be known which is not in my case... so let me know your view on the same... So, in simple words I need a query to handle 'n' hirerachy levels where 'n' is not known.....

@Damodaran 2013-11-26 12:00:45

@user3036105 Refer this link dba.stackexchange.com/questions/7147/…

@Damodaran 2013-11-26 12:16:38

@Damodaran 2013-11-26 12:24:33

@user3036105 Check my edited answer

@a_horse_with_no_name 2013-11-26 12:40:27

@user3036105: it is not possible to do this in MySQL with a single SQL query. MySQL simply isn't advanced enough for that. If you really need this, consider upgrading to a DBMS which supports recursive queries.

@Damodaran 2014-05-02 04:59:32

@Peter Nosko 2014-09-18 21:30:47

>Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. Maybe you meant a MySQL database. An Oracle database handles hierarchical data and queries quite well.

@Mark 2016-04-05 09:10:28

Thank you @Damodaran for the idea! ;)

@Dave L 2016-09-28 14:57:36

"...the management of hierarchical data is not what a relational database is intended for..." While this may have not been the original intention of a relational database, in the real-world hierarchical data is incredibly commonplace and MySQL should reflect how people actually need to use their data in real-world scenarios.

@philipxy 2017-02-06 22:30:55

Pictures of tables are 'flat'. Tables represent multidimensional relationships. That includes hierarchical relationships. It is DBMSs that don't supply relational support for that special case that are the problem, not the relational model.

@SOFe 2017-02-20 04:52:37

If you have to hardcode the number of levels it recursed into, it is not recursion.

@Barmar 2019-01-23 21:52:56

The Mike Hillyer site has gone away. Looks like he's moved his article behind a paywall at scribd.com/document/48207958/…

@trincot 2015-11-16 14:01:00

If you are on MySQL 8, then use the recursive with clause:

with recursive cte (id, name, parent_id) as (
  select     id,
             name,
             parent_id
  from       products
  where      parent_id = 19
  union all
  select     p.id,
             p.name,
             p.parent_id
  from       products p
  inner join cte
          on p.parent_id = cte.id
)
select * from cte;

The value specified in parent_id = 19 should be set to the id of the parent you want to select all the descendants of.

Before MySQL 8

For MySQL versions that do not support Common Table Expressions (up to version 5.7), you would achieve this with the following query:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv)
and     length(@pv := concat(@pv, ',', id))

Here is a fiddle.

Here, the value specified in @pv := '19' should be set to the id of the parent you want to select all the descendants of.

This will work also if a parent has multiple children. However, it is required that each record fulfills the condition parent_id < id, otherwise the results will not be complete.

Variable assignments inside a query

This query uses specific MySQL syntax: variables are assigned and modified during its execution. Some assumptions are made about the order of execution:

  • The from clause is evaluated first. So that is where @pv gets initialised.
  • The where clause is evaluated for each record in the order of retrieval from the from aliases. So this is where a condition is put to only include records for which the parent was already identified as being in the descendant tree (all descendants of the primary parent are progressively added to @pv).
  • The conditions in this where clause are evaluated in order, and the evaluation is interrupted once the total outcome is certain. Therefore the second condition must be in second place, as it adds the id to the parent list, and this should only happen if the id passes the first condition. The length function is only called to make sure this condition is always true, even if the pv string would for some reason yield a falsy value.

All in all, one may find these assumptions too risky to rely on. The documentation warns:

you might get the results you expect, but this is not guaranteed [...] the order of evaluation for expressions involving user variables is undefined.

So even though it works consistently with the above query, the evaluation order may still change, for instance when you add conditions or use this query as a view or sub-query in a larger query. It is a "feature" that will be removed in a future MySQL release:

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

As stated above, from MySQL 8.0 onward you should use the recursive with syntax.

Efficiency

For very large data sets this solution might get slow, as the find_in_set operation is not the most ideal way to find a number in a list, certainly not in a list that reaches a size in the same order of magnitude as the number of records returned.

Alternative 1: with recursive, connect by

More and more databases implement the SQL:1999 ISO standard WITH [RECURSIVE] syntax for recursive queries (e.g. Postgres 8.4+, SQL Server 2005+, DB2, Oracle 11gR2+, SQLite 3.8.4+, Firebird 2.1+, H2, HyperSQL 2.1.0+, Teradata, MariaDB 10.2.2+). And as of version 8.0, also MySQL supports it. See the top of this answer for the syntax to use.

Some databases have an alternative, non-standard syntax for hierarchical look-ups, such as the CONNECT BY clause available on Oracle, DB2, Informix, CUBRID and other databases.

MySQL version 5.7 does not offer such a feature. When your database engine provides this syntax or you can migrate to one that does, then that is certainly the best option to go for. If not, then also consider the following alternatives.

Alternative 2: Path-style Identifiers

Things become a lot easier if you would assign id values that contain the hierarchical information: a path. For example, in your case this could look like this:

ID       | NAME
19       | category1   
19/1     | category2  
19/1/1   | category3  
19/1/1/1 | category4  

Then your select would look like this:

select  id,
        name 
from    products
where   id like '19/%'

Alternative 3: Repeated Self-joins

If you know an upper limit for how deep your hierarchy tree can become, you can use a standard sql query like this:

select      p6.parent_id as parent6_id,
            p5.parent_id as parent5_id,
            p4.parent_id as parent4_id,
            p3.parent_id as parent3_id,
            p2.parent_id as parent2_id,
            p1.parent_id as parent_id,
            p1.id as product_id,
            p1.name
from        products p1
left join   products p2 on p2.id = p1.parent_id 
left join   products p3 on p3.id = p2.parent_id 
left join   products p4 on p4.id = p3.parent_id  
left join   products p5 on p5.id = p4.parent_id  
left join   products p6 on p6.id = p5.parent_id
where       19 in (p1.parent_id, 
                   p2.parent_id, 
                   p3.parent_id, 
                   p4.parent_id, 
                   p5.parent_id, 
                   p6.parent_id) 
order       by 1, 2, 3, 4, 5, 6, 7;

See this fiddle

The where condition specifies which parent you want to retrieve the descendants of. You can extend this query with more levels as needed.

@Byson 2015-12-24 10:57:38

I like your explanation. It doesn't just give an answer, it explains why it solves the problem so we can actually learn from it. EDIT: also it's great that it doesn't rely on knowing the number of levels beforehand.

@trincot 2015-12-24 11:34:36

@Bison, I very much appreciate your feed-back. Thanks!

@plong0 2016-03-23 17:04:24

Great answer @trincot! Thank you for the solution and summary of various approaches.

@trincot 2016-03-23 17:34:10

@plong0, I appreciate your ... appreciation ;-)

@Bobot 2016-05-17 13:35:19

@trincot Thx man, you just saved my day ! Btw this Question should be flagged as locked !

@trincot 2016-05-17 14:05:09

You're most welcome. Glad to hear it was of use to you!

@shreddish 2016-07-12 20:11:03

This answer should be flagged as correct... Thank you!

@trincot 2016-07-12 20:11:58

Nice to hear you liked it!

@Avión 2017-03-13 09:32:09

The This will work also if a parent has multiple children. part, where do I have to put that parent_id < id? Thanks in advance.

@trincot 2017-03-13 10:08:01

@Avión, it is not something you have to put somewhere, it is a requirement that for all records this condition is true. If you have one or more records where parent_id > id then you cannot use this solution.

@Avión 2017-03-13 10:10:56

I see. thanks. One little question. The first solution (the fist code) you're giving works perfectly, but if you have a depth more of 3 it doesnt get the childs. Do you know any way to make it work with a depth more of 3 levels? The last query works with more of 3 levels, but it's a bit more dirty. Thanks!

@trincot 2017-03-13 10:15:37

The first solution should work with any level. Can you make a fiddle that illustrates the problem you have with it? I will be happy to look at it.

@trincot 2017-03-13 10:33:15

@Avión, see this fiddle which has levels up to 5.

@Avión 2017-03-13 11:04:21

I see, I think it's the problem of the parent_id > id :(

@trincot 2017-03-13 11:15:36

In that case, you could consider rebuilding your data completely with newly generated keys -- if that is a possibility for you.

@shreddish 2017-07-19 14:54:03

@trincot Would it be possible to alter this to work in "reverse"? So grab all of a rows parents, grandparents, etc? I've used your first query for getting descendants but I would like to get the ancestors? as well.

@trincot 2017-07-19 14:59:59

Yes that would be possible and easier. If you get the logic used in this answer, it should not be hard to do that. Ask a new question if you bump into a problem.

@shreddish 2017-07-19 15:17:01

@trincot got it! haha thank you again for the post it was extremely helpful.

@Muhammad Rizwan 2017-08-18 07:43:44

thanks @trincot this really worked for me and good piece of work done by you.

@Horse 2017-11-16 16:58:12

For anyone looking to use the WITH RECURSIVE method, I found the following article really helpful with different scenarios such as recursion depth, distincts, and detecting and closing cycles

@fanfare 2017-12-10 05:38:45

if anyone else was looking for the answer to the question @shreddish asked, the solution is to change on p.parent_id = cte.id to on p.id = cte.parent_id

@Chirag Khatsuriya 2017-12-20 13:52:34

The first solution is not work for LEVEL with parent where parentID is null or 0. Here is my solution that I am trying to add LEVEL in query. select items_sorted.*, @pv := concat(@pv, ',', itemID), @lv as 'level',@parent from (select * from mst_item order by parentID, itemID) items_sorted, (select @pv := 16, @lv := 0,@parent:=0) initialisation where find_in_set(parentID, @pv) > 0 and IF(@parent<>parentID,@lv:[email protected]+1,@[email protected]) and IF(@parent<>parentID,@parent:=parentID,@parent:[email protected]) can you help me on this?

@sunil 2017-12-27 05:38:18

@trincot While using this with vb.net code this query is not returning expected result

@trincot 2017-12-27 17:23:42

@sumil, the query is executed by mysql, not vb.net. If you have a reproducible example, you should probably ask a new question, providing the table structure, sample data, the query, its oitput for that sample and the expected output.

@Dharmendra Singh 2018-01-27 09:48:10

@trincot and how can we retrieve parents from any child element. Above query is giving as expected result.

@trincot 2018-01-27 09:50:53

@DharmendraSingh, read the comments above. Have a try. If you bump into a problem, ask a new question.

@KC Wong 2018-02-01 09:54:59

I tried the main solution on MySQL5.7 on my computer, on my own tables, but that didn't work due to the equivalent of the clause @pv := concat(@pv, ',', id) evaluates to false. I fixed it by changing it to length(@pv := concat(@pv, ',', id)) > 0 so it is always true.

@trincot 2018-02-01 10:20:12

@KCWong, great! I put that improvement into the answer. Thanks!

@Christian Goetze 2018-03-28 20:21:48

Is there a way you can UNION the result of the last query without using temp tables?

@trincot 2018-03-29 04:57:32

@ChristianGoetze, I see no reason why not. Have a go at it and when you bump into a specific issue, I would suggest asking a new question about that.

@Yu Yenkan 2018-07-18 04:07:48

i prefer alternative 2, fast and easier

@Hariom 2018-12-20 11:24:11

Can anyone explain how to add where clause with second query(Before MySql 8) ? I want to remove editor usertype from result set but I don't know how to do this.

@Hariom 2018-12-20 12:03:31

I had tried but not getting expected result.

@Jhonatan Pereira 2019-01-18 16:32:39

Perfect! Very very very thanks and congrats!

@trincot 2019-01-18 16:33:30

@JhonatanPereira, nice to hear it is useful for you ;-)

@philipxy 2019-03-25 17:23:57

Pre-8 MySQL documentation explicitly says not to read & write the same variable in the same select statement. It then happens to say some vague stuff in implemention terms but does not coherently describe any syntax & behaviour that can be relied on. There is no justification for your code reading & writing the same variable. (People at Percona have shown by examining the code for particular versions that there is a certain safe idiom using case expressions.)

@trincot 2019-03-25 18:29:17

@philipxy, that is absolutely right, and I have already quoted the documentation on that. If you know of more explicit statements in the documentation, please let me know.

@Supun Kavinda 2019-05-16 00:30:05

Thanks for the answer. But, is there a way to limit results?

@Pradip Rupareliya 2019-01-07 09:17:07

enter image description here

It's a category table.

SELECT  id,
        NAME,
        parent_category 
FROM    (SELECT * FROM category
         ORDER BY parent_category, id) products_sorted,
        (SELECT @pv := '2') initialisation
WHERE   FIND_IN_SET(parent_category, @pv) > 0
AND     @pv := CONCAT(@pv, ',', id)

Output:: enter image description here

@Monzur 2018-12-13 12:37:40

This works for me, hope this will work for you too. It will give you a Record set Root to Child for any Specific Menu. Change the Field name as per your requirements.

SET @id:= '22';

SELECT Menu_Name, (@id:=Sub_Menu_ID ) as Sub_Menu_ID, Menu_ID 
FROM 
    ( SELECT Menu_ID, Menu_Name, Sub_Menu_ID 
      FROM menu 
      ORDER BY Sub_Menu_ID DESC
    ) AS aux_table 
    WHERE Menu_ID = @id
     ORDER BY Sub_Menu_ID;

@Der Zinger 2014-07-24 11:21:19

The best approach I've come up with is

  1. Use lineage to store\sort\trace trees. That's more than enough, and works thousands times faster for reading than any other approach. It also allows to stay on that pattern even if DB will change(as ANY db will allow that pattern to be used)
  2. Use function that determines lineage for specific ID.
  3. Use it as you wish (in selects, or on CUD operations, or even by jobs).

Lineage approach descr. can be found wherever, for example Here or here. As of function - that is what enspired me.

In the end - got more-or-less simple, relatively fast, and SIMPLE solution.

Function's body

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_lineage`(the_id INT) RETURNS text CHARSET utf8
    READS SQL DATA
BEGIN

 DECLARE v_rec INT DEFAULT 0;

 DECLARE done INT DEFAULT FALSE;
 DECLARE v_res text DEFAULT '';
 DECLARE v_papa int;
 DECLARE v_papa_papa int DEFAULT -1;
 DECLARE csr CURSOR FOR 
  select _id,parent_id -- @n:[email protected]+1 as rownum,T1.* 
  from 
    (SELECT @r AS _id,
        (SELECT @r := table_parent_id FROM table WHERE table_id = _id) AS parent_id,
        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := the_id, @l := 0,@n:=0) vars,
        table m
    WHERE @r <> 0
    ) T1
    where T1.parent_id is not null
 ORDER BY T1.lvl DESC;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    open csr;
    read_loop: LOOP
    fetch csr into v_papa,v_papa_papa;
        SET v_rec = v_rec+1;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- add first
        IF v_rec = 1 THEN
            SET v_res = v_papa_papa;
        END IF;
        SET v_res = CONCAT(v_res,'-',v_papa);
    END LOOP;
    close csr;
    return v_res;
END

And then you just

select get_lineage(the_id)

Hope it helps somebody :)

@Manish 2018-02-14 15:02:38

I have made a query for you. This will give you Recursive Category with a Single Query:

SELECT id,NAME,'' AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 WHERE prent is NULL
UNION 
SELECT b.id,a.name,b.name AS subName,'' AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id WHERE a.prent is NULL AND b.name IS NOT NULL 
UNION 
SELECT c.id,a.name,b.name AS subName,c.name AS subsubName,'' AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id WHERE a.prent is NULL AND c.name IS NOT NULL 
UNION 
SELECT d.id,a.name,b.name AS subName,c.name AS subsubName,d.name AS subsubsubName FROM Table1 AS a LEFT JOIN Table1 AS b ON b.prent=a.id LEFT JOIN Table1 AS c ON c.prent=b.id LEFT JOIN Table1 AS d ON d.prent=c.id WHERE a.prent is NULL AND d.name IS NOT NULL 
ORDER BY NAME,subName,subsubName,subsubsubName

Here is a fiddle.

@MTK 2018-01-21 01:28:23

Something not mentioned here, although a bit similar to the second alternative of the accepted answer but different and low cost for big hierarchy query and easy (insert update delete) items, would be adding a persistent path column for each item.

some like:

id | name        | path
19 | category1   | /19
20 | category2   | /19/20
21 | category3   | /19/20/21
22 | category4   | /19/20/21/22

Example:

-- get children of category3:
SELECT * FROM my_table WHERE path LIKE '/19/20/21%'
-- Reparent an item:
UPDATE my_table SET path = REPLACE(path, '/19/20', '/15/16') WHERE path LIKE '/19/20/%'

Optimise the path length and ORDER BY path using base36 encoding instead real numeric path id

 // base10 => base36
 '1' => '1',
 '10' => 'A',
 '100' => '2S',
 '1000' => 'RS',
 '10000' => '7PS',
 '100000' => '255S',
 '1000000' => 'LFLS',
 '1000000000' => 'GJDGXS',
 '1000000000000' => 'CRE66I9S'

https://en.wikipedia.org/wiki/Base36

Suppressing also the slash '/' separator by using fixed length and padding to the encoded id

Detailed optimization explanation here: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/

TODO

building a function or procedure to split path for retreive ancestors of one item

@lynx_74 2017-07-18 18:28:21

Simple query to list child's of first recursion:

select @pv:=id as id, name, parent_id
from products
join (select @pv:=19)tmp
where [email protected]

Result:

id  name        parent_id
20  category2   19
21  category3   20
22  category4   21
26  category24  22

... with left join:

select
    @pv:=p1.id as id
  , p2.name as parent_name
  , p1.name name
  , p1.parent_id
from products p1
join (select @pv:=19)tmp
left join products p2 on p2.id=p1.parent_id -- optional join to get parent name
where [email protected]

The solution of @tincot to list all child's:

select  id,
        name,
        parent_id 
from    (select * from products
         order by parent_id, id) products_sorted,
        (select @pv := '19') initialisation
where   find_in_set(parent_id, @pv) > 0
and     @pv := concat(@pv, ',', id)

Test it online with Sql Fiddle and see all results.

http://sqlfiddle.com/#!9/a318e3/4/0

@Saleh Mosleh 2017-05-22 04:10:24

Just use BlueM/tree php class for make tree of a self-relation table in mysql.

Tree and Tree\Node are PHP classes for handling data that is structured hierarchically using parent ID references. A typical example is a table in a relational database where each record’s “parent” field references the primary key of another record. Of course, Tree cannot only use data originating from a database, but anything: you supply the data, and Tree uses it, regardless of where the data came from and how it was processed. read more

Here is an example of using BlueM/tree:

<?php 
require '/path/to/vendor/autoload.php'; $db = new PDO(...); // Set up your database connection 
$stm = $db->query('SELECT id, parent, title FROM tablename ORDER BY title'); 
$records = $stm->fetchAll(PDO::FETCH_ASSOC); 
$tree = new BlueM\Tree($records); 
...

@Fandi Susanto 2017-03-11 10:45:32

Try these:

Table definition:

DROP TABLE IF EXISTS category;
CREATE TABLE category (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    parent_id INT,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id)
    REFERENCES category (id)
) engine=innodb;

Experimental rows:

INSERT INTO category VALUES
(19, 'category1', NULL),
(20, 'category2', 19),
(21, 'category3', 20),
(22, 'category4', 21),
(23, 'categoryA', 19),
(24, 'categoryB', 23),
(25, 'categoryC', 23),
(26, 'categoryD', 24);

Recursive Stored procedure:

DROP PROCEDURE IF EXISTS getpath;
DELIMITER $$
CREATE PROCEDURE getpath(IN cat_id INT, OUT path TEXT)
BEGIN
    DECLARE catname VARCHAR(20);
    DECLARE temppath TEXT;
    DECLARE tempparent INT;
    SET max_sp_recursion_depth = 255;
    SELECT name, parent_id FROM category WHERE id=cat_id INTO catname, tempparent;
    IF tempparent IS NULL
    THEN
        SET path = catname;
    ELSE
        CALL getpath(tempparent, temppath);
        SET path = CONCAT(temppath, '/', catname);
    END IF;
END$$
DELIMITER ;

Wrapper function for the stored procedure:

DROP FUNCTION IF EXISTS getpath;
DELIMITER $$
CREATE FUNCTION getpath(cat_id INT) RETURNS TEXT DETERMINISTIC
BEGIN
    DECLARE res TEXT;
    CALL getpath(cat_id, res);
    RETURN res;
END$$
DELIMITER ;

Select example:

SELECT id, name, getpath(id) AS path FROM category;

Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 19 | category1 | category1                               |
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
| 23 | categoryA | category1/categoryA                     |
| 24 | categoryB | category1/categoryA/categoryB           |
| 25 | categoryC | category1/categoryA/categoryC           |
| 26 | categoryD | category1/categoryA/categoryB/categoryD |
+----+-----------+-----------------------------------------+

Filtering rows with certain path:

SELECT id, name, getpath(id) AS path FROM category HAVING path LIKE 'category1/category2%';

Output:

+----+-----------+-----------------------------------------+
| id | name      | path                                    |
+----+-----------+-----------------------------------------+
| 20 | category2 | category1/category2                     |
| 21 | category3 | category1/category2/category3           |
| 22 | category4 | category1/category2/category3/category4 |
+----+-----------+-----------------------------------------+

@Basheer Ahmed 2018-04-03 06:54:02

This won't work for more then one child. e.g (20, 'category2', 19), (21, 'category3', 20), (22, 'category4', 20),

@Fandi Susanto 2018-04-07 14:12:03

I'm pretty sure it works for more than one child. I even tested it again.

@Dogan Ozer 2019-03-09 06:34:27

@Fandi Susanto , Thanks it alote helps me.

@Justin Howard 2016-01-13 17:55:47

If you need quick read speed, the best option is to use a closure table. A closure table contains a row for each ancestor/descendant pair. So in your example, the closure table would look like

ancestor | descendant | depth
0        | 0          | 0
0        | 19         | 1
0        | 20         | 2
0        | 21         | 3
0        | 22         | 4
19       | 19         | 0
19       | 20         | 1
19       | 21         | 3
19       | 22         | 4
20       | 20         | 0
20       | 21         | 1
20       | 22         | 2
21       | 21         | 0
21       | 22         | 1
22       | 22         | 0

Once you have this table, hierarchical queries become very easy and fast. To get all the descendants of category 20:

SELECT cat.* FROM categories_closure AS cl
INNER JOIN categories AS cat ON cat.id = cl.descendant
WHERE cl.ancestor = 20 AND cl.depth > 0

Of course, there is a big downside whenever you use denormalized data like this. You need to maintain the closure table alongside your categories table. The best way is probably to use triggers, but it is somewhat complex to correctly track inserts/updates/deletes for closure tables. As with anything, you need to look at your requirements and decide what approach is best for you.

Edit: See the question What are the options for storing hierarchical data in a relational database? for more options. There are different optimal solutions for different situations.

@Phil John 2015-11-21 14:42:23

You can do it like this in other databases quite easily with a recursive query (YMMV on performance).

The other way to do it is to store two extra bits of data, a left and right value. The left and right value are derived from a pre-order traversal of the tree structure you're representing.

This is know as Modified Preorder Tree Traversal and lets you run a simple query to get all parent values at once. It also goes by the name "nested set".

@Miroslaw Opoka 2017-02-27 17:07:44

I wanted to add a similar comment to yours, but since you did it, I will add just a link to a good example of the "nested set": mikehillyer.com/articles/managing-hierarchical-data-in-mysql

@cripox 2015-09-17 19:25:34

I found it more easily to :

1) create a function that will check if a item is anywhere in the parent hierarchy of another one. Something like this (I will not write the function, make it with WHILE DO) :

is_related(id, parent_id);

in your example

is_related(21, 19) == 1;
is_related(20, 19) == 1;
is_related(21, 18) == 0;

2) use a sub-select , something like this:

select ...
from table t
join table pt on pt.id in (select i.id from table i where is_related(t.id,i.id));

@senK 2013-11-27 05:45:05

Its a little tricky one, check this whether it is working for you

select a.id,if(a.parent = 0,@varw:=concat(a.id,','),@varw:=concat(a.id,',',@varw)) as list from (select * from recursivejoin order by if(parent=0,id,parent) asc) a left join recursivejoin b on (a.id = b.parent),(select @varw:='') as c  having list like '%19,%';

SQL fiddle link http://www.sqlfiddle.com/#!2/e3cdf/2

Replace with your field and table name appropriately.

@Jaugar Chang 2014-09-12 12:34:00

It won't work in this case sqlfiddle.com/#!2/19360/2, with this trick, at least you should order by hierarchical level first.

Related Questions

Sponsored Content

29 Answered Questions

[SOLVED] How to output MySQL query results in CSV format?

  • 2008-12-10 15:59:51
  • MCS
  • 1053504 View
  • 1051 Score
  • 29 Answer
  • Tags:   mysql csv quotes

36 Answered Questions

[SOLVED] Should I use the datetime or timestamp data type in MySQL?

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

11 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

7 Answered Questions

22 Answered Questions

[SOLVED] How to reset AUTO_INCREMENT in MySQL?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

33 Answered Questions

[SOLVED] How to query MongoDB with "like"?

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

Sponsored Content