Recuring Join?!?!

[Anon]

A strange request...

I have a table with the following:

id
name
parent

It's used to store a menu. Anything with parent = 0 is top-level, and the others, the parent is the id of it's parent (get it?).

However, it's kinda unlimited levels deep, and currently I loop around it 5 times (so only 5 levels deep show up), but I'm sure there's gotta be a way do do the whole thing, so it takes a parent, all it's children, then their children etc...

Any offers?

Thanks,

Danny

[[deleted]]

That requires a recursive function, which can generate heaps of queries.

If you want to list 3 'trees' of 6 levels each, it will take 3 * 6 = 18 queries.
if you list 20 trees of 10 levels; 200 queries. ouch.

One way to cheat would be to simply read all the rows into an array inmemory, and do the recursive functions there, which is much faster.

Another 'cheat' is to add another column to the table, called 'threadid' or 'grandparent'
This field wil hold the id of the 'top-level' row for each child.
That way you can limit the number of rows to fetch to just those that have the correct grandparent, and recursively process those.

[Anon]

I know it's possible with loads of queries, but wanted to avoid that. The "grandparent" idea sounds good, it'd use less queries, and then I sort the tree with php. I guess if I wanted to start from a non-top level item, that wouldn't work, but since I'm only created trees of the whole structure, it should do nicely.

Thanks for the quick reply (and all the other quick replies you've given me!)

[kparker]

Depends on which order you want them in. If you need:

parent
   child
      grandchild
parent
   child
   child
      grandchild

and you're not using Oracle or DB/2, you're out of luck as far as a single query goes. Those (and perhaps a few other Big Boys) have some extensions that provide for recursive queries. Otherwise you do as vincent says.