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 thoughts on “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:

    http://www.sitepoint.com/article/hierarchical-data-database/

    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.

    Thanks!

  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.

    Sincecerely,

    Ryan Helms

    CTO, Qube Technologies and Media, LLC.

Leave a Reply

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