Connect By Prior Equivalent for MySQL

MySQL doesn't support recursive queries so you have to do it the hard way:

Select the rows where ParentID = X where X is your root.
Collect the Id values from (1).
Repeat (1) for each Id from (2).
Keep recursing by hand until you find all the leaf nodes.

If you know a maximum depth then you can join your table to itself (using LEFT OUTER JOINs) out to the maximum possible depth and then clean up the NULLs.

You could also change your tree representation to nested sets.

Might be late post.

With MySQL8 you can achieve it with recursive clause. Here is the example.

 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;

For more help find another thread, Hope It will help someone.

You Can also look into this interesting blog, which demonstrate how can we get similar results in mysql

http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/

This is an old thread, but since I got the question in another forum I thought I'd add it here. For this case, I created a stored procedure that is hard-coded to handle the specific case. This do, of course have some drawbacks since not all users can create stored procedures at will, but nevertheless.

Consider the following table with nodes and children:

CREATE TABLE nodes (
       parent INT,
       child INT
);

INSERT INTO nodes VALUES
       ( 5,  2), ( 5, 3),
       (18, 11), (18, 7),
       (17,  9), (17, 8),
       (26, 13), (26, 1), (26,12),
       (15, 10), (15, 5),       
       (38, 15), (38, 17), (38, 6),
       (NULL, 38), (NULL, 26), (NULL, 18);

With this table, the following stored procedure will compute a result set consisting of all the decedents of the node provided:

delimiter $$
CREATE PROCEDURE find_parts(seed INT)
BEGIN
  -- Temporary storage
  DROP TABLE IF EXISTS _result;
  CREATE TEMPORARY TABLE _result (node INT PRIMARY KEY);

  -- Seeding
  INSERT INTO _result VALUES (seed);

  -- Iteration
  DROP TABLE IF EXISTS _tmp;
  CREATE TEMPORARY TABLE _tmp LIKE _result;
  REPEAT
    TRUNCATE TABLE _tmp;
    INSERT INTO _tmp SELECT child AS node
      FROM _result JOIN nodes ON node = parent;

    INSERT IGNORE INTO _result SELECT node FROM _tmp;
  UNTIL ROW_COUNT() = 0
  END REPEAT;
  DROP TABLE _tmp;
  SELECT * FROM _result;
END $$
delimiter ;

The below select lists all plants and their parentid up to 4-level (and of course you can extend the level):

select id, name, parentid
,(select parentid from tb_tree where id=t.parentid) parentid2
,(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid)) parentid3
,(select parentid from tb_tree where id=(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid))) parentid4 
from tb_tree t

and then you can use this query to get the final result. for example, you can get all children of "Fruits" by the below sql:

select id ,name from (
    select id, name, parentid
    ,(select parentid from tb_tree where id=t.parentid) parentid2
    ,(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid)) parentid3
    ,(select parentid from tb_tree where id=(select parentid from tb_tree where id=(select parentid from tb_tree where id=t.parentid))) parentid4 
    from tb_tree t) tt
where ifnull(parentid4,0)=1 or ifnull(parentid3,0)=1 or ifnull(parentid2,0)=1 or ifnull(parentid,0)=1

Connect By Prior Equivalent for MySQL

Related

Recent Posts