Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2009
    Posts
    4

    Exclamation 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. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Is this an assignment to see how much the students have grokked recursive SQL?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2009
    Posts
    4

    Exclamation 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. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, I'm sure that your tutor, your TA, or your professor can help you with this. If we understood what you'd already covered in class and what this assignment was trying to help you learn, we could probably help you too. Since you are already paying at least the professor and possibly others to help you learn, you really ought to use the services that you've already paid for instead of struggling with this on your own.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote 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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  10. #10
    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. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote 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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote 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
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote 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.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rakesh,

    If I give you the answers so you can graduate, will you stick my name on the diploma?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •