Finding All Children

[BlaineSch]

Okay so lets say my table looks like this:

id,   name, parent
 1,   root, null
 2, group1, 1
 3, group2, 1
 4, group3, 1
 5, group4, 2
 6, group4, 8

So let's say if I know the root's ID number, it's really easy to get that ID and then immediately all it's children

SELECT * FROM `table` WHERE `parent`=1

That will return group1,group2, and group3. But now lets say I want to get root's children and their children recursively. Is there a way to do this with a single query? Or would I have to do a script to get kids and for each group do another query until I run out of kids?

Maybe you could propose another table structure to help efficiency.

[Orjan]

I find your table structure fine as it is. hard to make something more normalized. I'll go with the digging thing in a script, everything depending on how you want the data in the end, how do you want it? are you building a tree structure, or do you want them flat as soon as you got them from the tree?

[BlaineSch]

I'm really just trying to find the most efficient way of telling if "group4" is a child of "group1"

If the chain goes really far down (100+ nodes) then that would require a query for each level and would obviously take up a lot of resources.

[amrosama]

hmm...
i dont know if theres a way to do it in a single query, i would handle it with a little help from php

[Orjan]

I would do this in php yes. but 100 levels doesn't take that huge amount of resources anyway. it's not end of the world. but. you want to start with the child (if you know it) and climb the ladder instead of doing it up-to-down. this way, you climb more limited, until parent == 0, as you can't know if a row is parent or not.