Connect By Prior Equivalent for MySQL

All,

I have three fields in a table that define a parent child relationship present in a MySQL d开发者_开发知识库atabase version 5.0 . The table name is tb_Tree and it has the following data:

Table Name: tb_Tree

Id | ParentId | Name
--------------------
1  | 0        | Fruits
2  | 0        | Vegetables
3  | 1        | Apple
4  | 1        | Orange
5  | 2        | Cabbage
6  | 2        | Eggplant

How do I write a Query to get all the children if a ParentId is specified. Note that the table entries given are just sample data and they can have many more rows. Oracle has a "CONNECT BY PRIOR" clause, but I didn't find anything similar for MySQL. Can anyone please advise?

Thanks

---

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

1. Select the rows where ParentID = X where X is your root.
2. Collect the Id values from (1).
3. Repeat (1) for each Id from (2).
4. 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

---

The below stored procedure order a table that has rows with back reference to the previous one. Notice on the first step I copy rows into temp table - those rows match some condition. In my case those are rows that belong to the same linear (road that is used in GPS navigation). Business domain is not important. Just in my case I am sorting segments that belong to the same road

DROP PROCEDURE IF EXISTS orderLocations; DELIMITER //

CREATE PROCEDURE orderLocations(_full_linear_code VARCHAR(11)) BEGIN

DECLARE _code VARCHAR(11);
DECLARE _id INT(4);
DECLARE _count INT(4);
DECLARE _pos INT(4);

DROP TEMPORARY TABLE IF EXISTS temp_sort;

CREATE TEMPORARY TABLE temp_sort (
  id              INT(4) PRIMARY KEY,
  pos             INT(4),
  code            VARCHAR(11),
  prev_code       VARCHAR(11)
);

-- copy all records to sort into temp table - this way sorting would go all in memory
INSERT INTO temp_sort SELECT
                         id, -- this is primary key of original table
                         NULL, -- this is position that still to be calculated
                         full_tmc_code, -- this is a column that references sorted by
                         negative_offset -- this is a reference to the previous record (will be blank for the first)
                       FROM tmc_file_location
                       WHERE linear_full_tmc_code = _full_linear_code;

-- this is how many records we have to sort / update position
SELECT count(*)
FROM temp_sort
INTO _count;

-- first position index
SET _pos = 1;

-- pick first record that has no prior record
SELECT
  code,
  id
FROM temp_sort l
WHERE prev_code IS NULL
INTO _code, _id;

-- update position of the first record
UPDATE temp_sort
SET pos = _pos
WHERE id = _id;

-- all other go by chain link
WHILE (_pos < _count) DO
  SET _pos = _pos +1;

  SELECT
    code,
    id
  FROM temp_sort
  WHERE prev_code = _code
  INTO _code, _id;


  UPDATE temp_sort
  SET pos = _pos
  WHERE id = _id;

END WHILE;

-- join two tables and return position along with all other fields
SELECT
  t.pos,
  l.*
FROM tmc_file_location l, temp_sort t
WHERE t.id = l.id
ORDER BY t.pos;

END;