hierachical data in table and queries

 Root Node will have parentId zero

create table hierarchy(

id int primary key,

name varchar(50),

value varchar(50),

parentId int,

hpath varchar(100)

)

insert into hierarchy(id,name,value,parentId,hpath) values(1,'abc','abc',0,'/1');

insert into hierarchy(id,name,value,parentId,hpath) values(2,'lmn','lmn',1,'/1/2');

insert into hierarchy(id,name,value,parentId,hpath) values(3,'anp','anp',0,'/3');

insert into hierarchy(id,name,value,parentId,hpath) values(4,'abp','abp',3,'/3/4');

insert into hierarchy(id,name,value,parentId,hpath) values(5,'abl','abl',4,'/3/4/5');

insert into hierarchy(id,name,value,parentId,hpath) values(6,'abq','abq',5,'/3/4/5/6');

insert into hierarchy(id,name,value,parentId,hpath) values(7,'abo','ab0',1,'/1/7');

insert into hierarchy(id,name,value,parentId,hpath) values(8,'abk','abk',0,'/8');

insert into hierarchy(id,name,value,parentId,hpath) values(9,'wbl','wbl',4,'/3/4/9');

Find count of childrens for each parent node

select

t2.id,

count(*)

from

hierarchy t1

inner join hierarchy t2 on t1.parentId = t2.id

group by

t2.id

Find Parent and its child

select parentId,group_concat(id) from hierarchy group by parentId

Find list of childrens of root node

select id,count(*)

from hierarchy

where id in (select id from hierarchy where parentId=0)

group by

id;

Find all childrens of node 1

select * from hierarchy where hpath like '/1/%'

Find sibling of 5

select * from hierarchy where parentid=(select parentId from hierarchy

where id=5);