Parent, Child Query | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Parent, Child Query

I have a table with two fields "child" and "parent"., and I have some data on it. for example child parent
——- ———
a <null>
b <null>
c a
d b
e a
f b
g c
h d
i g
j h
k i
l a
m l
n l
o a
p c I want to collect the child list of a parent, on a recordset of VB.
How can I do it on a single SQL statement or Stored Procedure ? Also I want the child list of who’s parent field is null. for example
1) I know the a and I want the chil list of a
a has the child c, and e, l, o,g,p,i,k,m,n 2) I know n and, I want the parents list of n 3) I know some records have null parent, so it’s and it’s child list. I am not interested on ADOMD (Multidimentional).
Kindly help me

This is a common problem in SQL. Although SQL deals with relational data very well, it still has some shortcomings when dealing with hierarchical data where the number of generations is unknown at design-time. But lots of very good articles has been written on how you can achieve this. Please see for joe celko’s famous(?) article on the topic. Also a google search for ‘hierarchy sql server’ will bring up many articles.