Does anyone know SQL for this query?

[mscertified]

Table structure:
ID
Item
ParentID (points to ID)

I need a query that will return me every combination of
ParentID and ID for every level in the structure, e.g.

Data
1 A 0
2 B 1
3 C 2

Will return:
1 2
1 3
2 3

In other words if this were a family tree, I need to be able to find out all
of a persons ancestors and all their descendents.

Solution must work no matter how many levels there are.
Any help appreciated.

 

[Tom Ellison]

Dear Certifiable:

I will assume the first column in Data is the ID, and the third column is
the ParentID. You have a self referencing table with no skipped
generations. The middle column would be ranking the generations, right?

A query like this finds parent:

SELECT T1.ID, T2.ID as Child
FROM Data T1, Data T2
WHERE T2.ParentID = T1.ID

This gives the first generation change, that is, results 1 2 and 2 3

Second generation is:

SELECT T1.ID, T3.ID as Child
FROM Data T1, Data T2, Data T3
WHERE T2.ParentID = T1.ID
AND T3.ParentID = T2.ID

This should give the second generation result 1 3.

For more generations, this gets a little tougher each level. How far do you
need it to go?

You can combine all these queries into a UNION ALL to have a single set of
results. Been there, done that!

Tom Ellison

 

[mscertified]

Thanks for the response.
I had already figured that out but was hoping for a solution for an
unlimited number of levels.

 

[Tom Ellison]

Dear Certifiable:

That would require recursion, which I have not found possible in SQL. What
you have is the best I can offer.

Tom Ellison