Mysql query not getting all parent ids

Question

I want to fetch all the child ids (comma separeted) but my code just getting the two level heirachy, not more than that. I dont know whats issue? here is my code

 select concat (a.id,',',a.parent_id) as parents
  from table1 a 
 inner join  table1 b 
    on a.id = b.parent_id
 where b.id = 5

Here is my table structure:

    id | parent_id
    ---------------------
     1 |      6
     2 |      NULL
     3 |         1
     4 |         3
     5 |         4
     6 |      NULL
     7 |      NULL

output expected:

4,3,1,6

This question provide a solution with dynamic levels [Getting all parent rows in one SQL query](http://stackoverflow.com/questions/2441821/getting-all-parent-rows-in-one-sql-query) — sagi, Jun 21 '16 at 07:36
The child ids would be 1, 3, 4, 5. Perhaps you want the parent_ids — Strawberry, Jun 21 '16 at 07:43

score 0 · Answer 1 · answered Jun 21 '16 at 08:19

0

Try

SELECT GROUP_CONCAT(@temp:=T.parent_id) as parents
FROM (SELECT * FROM testTable ORDER BY id DESC) T
 JOIN
 (SELECT @temp:=5)tmp
WHERE T.id=@temp;

Refer : SQL Fiddle

answered Jun 21 '16 at 08:19

zakhefron

1,403
1
9
13

Mysql query not getting all parent ids

1 Answers1