SQL "Tree" Query

[UberError]

Suppose I have a table that can hold a virtual directory with an unlimited amount of nodes.

nodeID int primary key
nodeName varchar(50)
parentID int

parentID refers back to id and when the parentID is null that would signify a root record

Can anyone think of ONE query that takes the nodeID and returns ALL of the child nodes in some sort of order? Again, I just want ONE query... no loops, no temp tables.

I would imagine this is a common problem. Is there a common solution?

Thanks!

[StupidRalph]

I don't know the exact SQL you're looking for, but try searching Google for Modified Preorder Tree Traversal (MPTT). There is a MPTT class in the post a PHP snippet forum.

[websquid]

mysql doesn't do recursive queries.. i know it sucks.. maybe if we hound them enough they'll buckel under the pressure :rolleyes: