Getting all the children (and their children) of a given parent node, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

-- select version() as 'mysql version'
drop table tchilds;

create table tchilds

(
parent int , child int );

insert into tchilds

select 0 , 2 union all select
0 ,8 union all select
2 ,3 union all select
2 ,6 union all select
3 ,4 union all select
3 ,5 union all select
6 ,7 union all select
6 ,9 union all select
9 ,10 
;

/* Find only fist parent

select @pv:=parent as parent, child from tchilds
join
(select @pv:=6)tmp
where parent=@pv

/* Getting all the children (and their children) of a given parent node  in MYSQL */

/* display only child with comma seperate */

select * from tchilds ;

select GROUP_CONCAT(concat (child )SEPARATOR ',') as Child

from    (select * from tchilds
         order by parent, child) s,
        (select @pv := '6') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', child);

/* dispaly Parnet and child with comma separte within { } */

select  Parent, concat ( "{" ,Parent,",",GROUP_CONCAT(concat (child )SEPARATOR ','),"}")   as Child

from    (select * from tchilds
         order by parent, child) s,
        (select @pv := '6') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', child);

/* display parent + child with comma seperate */

select  @pv as Child
from    (select * from tchilds
         order by parent, child) s,
        (select @pv := '6') initialisation
where   find_in_set(parent, @pv) > 0
and     @pv := concat(@pv, ',', child)
order by @pv DESC LIMIT 1;

/* display  only child into one column */

select  Child
from    (select * from tchilds
         order by parent, child) s,
        (select @pv2 := '6') initialisation
where   find_in_set(parent, @pv2) > 0
and     @pv2 := concat(@pv2,',', child);

/* display  parnet and child into one column */

select parent as child from tchilds where parent = @pv2
union
select  Child
from    (select * from tchilds
         order by parent, child) s,
        (select @pv2 := '6') initialisation
where   find_in_set(parent, @pv2) > 0
and     @pv2 := concat(@pv2,',', child)

/* http://stackoverflow.com/questions/41113828/getting-all-the-children-and-their-children-of-a-given-parent-node-in-a-mysql */


absolute service time: 0,34 sec