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.
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”
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.
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!
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.