Find All Children of Parent in Stored Procedure

Given a Parent ID, what is the best way to return all child records, their child records, ad inifitum?

I needed this to find all employees beneath a given manager no matter how deep.


3 responses to “Find All Children of Parent in Stored Procedure”

  1. Hey, have you heard of modified preorder traversal? It’s a different way to lay out the table schema so that queries like these are much easier (and faster).

    I’ve used it a few times. Pretty fun:

    One thing to watch out for that I got bitten by is that you can’t easily get all the children at a certain level below a node. Like if you want to find all grandchildren (not children and not great-grandchildren), there’s no easy way to do it.

  2. Nice Aaron. I had read on that on a different website, but didn’t want to go through the hassle of writing scripts to auto-update the numbering system.

    What is cool about that link is they have that for you (I hate reinvented the wheel), even though it is in perl.


  3. Scott,

    As a pretty experienced developer, I came across the same exact issue that you were having with your infinite employees under a manager … thanks to you, I no longer have a problem. Well done and thank you.


    Ryan Helms

    CTO, Qube Technologies and Media, LLC.

Leave a Reply

Your email address will not be published. Required fields are marked *