# Thread: loop through hierarchy data

1. Registered User
Join Date
Oct 2009
Posts
4

## Unanswered: loop through hierarchy data

can someone solve my problem :

consider some inventory data:

AssemblyKey AssemblyName ParentKey
0 System NULL
1 System NULL
2 SubAssembly 0
3 SubSubAssembly 2
4 SubAssembly 1
5 SubAssembly 0

In this case, we have two systems (ParentKey is NULL) with the following descendents

System (key = 0)
> Sub assembly (key =2)
> Sub sub assembly (key=3)
> Sub assembly (key=5)

System (key=1)
> sub assembly (key=4)

How would you write a query to list the total number of descendents for each system, i.e., ?

AssemblyKey DescendentCount
0 3
1 1

i need the count for all descendents of root parents
plz help !!!!!!

2. Registered User
Join Date
Nov 2004
Posts
1,428
Is this an assignment to see how much the students have grokked recursive SQL?

3. Registered User
Join Date
Oct 2009
Posts
4

## loop through hierarchy data

can someone solve my problem :

consider some inventory data:

AssemblyKey AssemblyName ParentKey
0 System NULL
1 System NULL
2 SubAssembly 0
3 SubSubAssembly 2
4 SubAssembly 1
5 SubAssembly 0

In this case, we have two systems (ParentKey is NULL) with the following descendents

System (key = 0)
> Sub assembly (key =2)
> Sub sub assembly (key=3)
> Sub assembly (key=5)

System (key=1)
> sub assembly (key=4)

How would you write a query to list the total number of descendents for each system, i.e., ?

AssemblyKey DescendentCount
0 3
1 1

i need the count for all descendents of root parents
plz help !!!!!!

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579

-PatP

5. Registered User
Join Date
Oct 2009
Posts
4
i cannot ask my tutor at present
i need it desperately plz help me out man !!

6. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
You can adapt this method to get a count, rather than returning the actual records:
sqlblindman - Returning Child Records
Note that in Oracle and SQL Server 2005 there is a construct called a Common Table Expression which implements more direct recursive processing.

7. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
The reason that you have the tutor is to help you work through assignments, that is why you pay them. You ought to be able to ask your tutor anything about an assignment at any time.

-PatP

8. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Blindman: I just merged the threads, it made more sense to me this way than leaving one locked.

-PatP

9. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by Pat Phelan
Blindman: I just merged the threads, it made more sense to me this way than leaving one locked.

-PatP
I don't know what is causing this, but I can't see the posts of this thread in FireFox 3.5, only in IE 8.

10. Registered User
Join Date
Oct 2009
Posts
4
thanks for the advice guys but i want to impress my tutor and he is kinda bummer u can say ,thats why i am not asking him
after all its all about the grades(ya gaining knowledge is also important)
Last edited by rakesh252378; 10-07-09 at 17:42.

11. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Originally Posted by rakesh252378
thanks for the advice guys but i want to impress my tutor and he is kinda bummer u can say ,thats why i am not asking him
after all its all about the grades(ya gaining knowledge is also important)
Stop with the bullcrap, rakesh. The way to impress a tutor or a teacher is to demonstrate interest in learning.
I can tell you that statements like that neither impress nor fool anyone on this forum.

12. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by Wim
I don't know what is causing this, but I can't see the posts of this thread in FireFox 3.5, only in IE 8.
I'm running U3 FireFox 3.5.3 and can read all posts nicely. Maybe you need to update your copy of FireFox ?

-PatP

13. Registered User
Join Date
Nov 2004
Posts
1,428
Originally Posted by Pat Phelan
I'm running U3 FireFox 3.5.3 and can read all posts nicely. Maybe you need to update your copy of FireFox ?
I am running FireFox 3.5.3.

I can see the hierarchy of all posts on this thread:
* The 7 first are with a pale blue background, those posts I can read.
* The last 5 have a white background, I can't read those, not even when I click one of them.

Now that I am editing this post I can see all the 12 post in the Topic Review part.

I'm guessing this post will end up in the "blue" part.

14. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445