Mysql query not getting all parent ids [duplicate]

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 — sagi
– sagi, Commented Jun 21, 2016 at 7:36
The child ids would be 1, 3, 4, 5. Perhaps you want the parent_ids — Strawberry
– Strawberry, Commented Jun 21, 2016 at 7:43

zakhefron · Accepted Answer · 2016-06-21 08:19:02Z

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, 2016 at 8:19

zakhefron

1,4431 gold badge9 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql query not getting all parent ids [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related