Query to get children records of a parent

Question

I have following table.

+-------+--------+-------+
|     Id|    Name| Parent|
+-------+--------+-------+
|      0|       A|     -1|
|      1|       B|      0|
|      2|       C|      1|
|      3|       D|      2|
|      4|       E|      2|
|      5|       F|      2|
|      6|       G|      2|
|      7|       H|      2|
|      8|       I|      2|
+-------+--------+-------+

-1 as parent means it has no parent. The hierarchy can go upto 7-8 levels. I need to get all the children (direct + indirect). I tried something like:

SELECT Id, NAME, @pv:=Id AS 'Parent' FROM tbl
JOIN
(SELECT @pv:=-1)tmp
WHERE Parent=@pv

But it does not work.

@TheProvost, list of all the children i.e. for `A`, I expect records `B` to `I`. Since B is child of A. C is child of B and rest are children of C. — benjamin54, Nov 14 '14 at 04:00
oh then i suggest you use CTE. its like a subquery but you can call it recursively which is exactly what you need. — TheProvost, Nov 14 '14 at 04:06
This might be helpful: http://stackoverflow.com/questions/20215744/mysql-hierarchical-recursive-query — sgeddes, Nov 14 '14 at 04:16
@a_horse_with_no_name oh sorry i did not know that. Well you can always create a stored procedure and call itself recursively — TheProvost, Nov 14 '14 at 07:59

score 0 · Answer 1 · edited May 23 '17 at 11:58

0

try this query

SELECT t1.name AS lev1, t2.name as lev2
FROM tbl1 AS t1
LEFT JOIN tbl1 AS t2 ON t2.parent = t1.id
WHERE t1.id = 2;

expected output

+-------+--------+
|   lev1|    lev2|
+-------+--------+
|      C|       D|
|      C|       E|
|      C|       F|
|      C|       G|
|      C|       H|
|      C|       I|
+-------+--------+

Referance

edited May 23 '17 at 11:58

Community

1
1

answered Jul 08 '16 at 14:22

Elyor

5,396
8
48
76

Query to get children records of a parent

1 Answers1