Backwards tree parsing
Posted: Fri Aug 26, 2005 11:44 pm
Hi,
We have a tree structure data table, for storing heirarchical data...
Parent
...Child
...Child
......Child of Child
...Child
..........Child of Child of Child
with an infinite possibility of children. Now, I'd like to using SQL enter a SELECT a parent ID and have all it's children (and childrens children...etc) all returned as well.
I know I can use SELECT in SELECT to do one tier, but how can I have it recursively check under that. I seem to recall this functionality somewhere being told to me.
I'm confident I could do this using a PHP recursive loop, but using SQL seems so much more elegant... plus I'm just curious how to do it anyway.
Before you warn me: I'm well aware of the system load doing this with lots of children, however, this is a minimal database, without too much data in it at all. Plus, we wouldn't be selecting parent's way down towards generation 1 of the tree anyway. Probably returning between 10-30 rows working properly.
Thanks as always,
Josh
We have a tree structure data table, for storing heirarchical data...
Parent
...Child
...Child
......Child of Child
...Child
..........Child of Child of Child
with an infinite possibility of children. Now, I'd like to using SQL enter a SELECT a parent ID and have all it's children (and childrens children...etc) all returned as well.
I know I can use SELECT in SELECT to do one tier, but how can I have it recursively check under that. I seem to recall this functionality somewhere being told to me.
I'm confident I could do this using a PHP recursive loop, but using SQL seems so much more elegant... plus I'm just curious how to do it anyway.
Before you warn me: I'm well aware of the system load doing this with lots of children, however, this is a minimal database, without too much data in it at all. Plus, we wouldn't be selecting parent's way down towards generation 1 of the tree anyway. Probably returning between 10-30 rows working properly.
Thanks as always,
Josh