parent child - same table - list all / below in hirarachy

table structure:

id   pid
1   0
2   0
3   0
4   1
5   2
6   3
7   4
8   4
9   3

Relations/ hierarchy :--
(hierarchy may deepen to level four as well..)

I want to delete all records from that level and blow the hierarchy.
in case of 4, query should be: (obtain 4 and sub-sub-child of 4)
    -- delete from table-name where id in (4,7,8);
in case of 3, query should be giving: (3,child and sub-child....)
    --> delete from table-name where id in (3,6,9); ####
in case of 9 it should only delete record id=9.

I'm trying this with single query - self join but its difficult to obtain as level of hierarchy may differ.

Can any one provide me with suitable solution!!

LVL 21
K VDatabase ConsultantAsked:
Who is Participating?
K VConnect With a Mentor Database ConsultantAuthor Commented:
i got that by query its not psble, i tried cursor but it seems for higher number of data, it will slow down the performance. i'll go for procedure/function.
mahomeConnect With a Mentor Commented:
As level of hierarchy may differ you can't do it with a single query. You have to use a loop in mysql function or any programming language.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.