areas & subareas

[Anon]

I'm trying to solve a problem... I have a site divided in areas. Each area has it's own id, and may have it's own sub-areas. Each sub-area has it's own sub-areas, etc...

My will is to return the areas in some cascade order... I can do it recursively, but it generates too many queries. I was wondering how i could do this with only one query to the database.

Here's the structure i have so far:
AREAS (area_id INT; parent_id INT; root_id(??) INT; description VARCHAR(60));

Can anyone help?? Thanks in advance...

PS: I think this would very similar to a threaded forum...

[Anon]

Is there any limit to how deep the sub-areas are nested? If not, then recursive is your only choice. There are some dbms that do support sql extensions for direct depth-first recursive retrieval in a single query, but they are spelled 'oracle' and 'db2'.

Otherwise, with limited depth, you can reduce the the problem to n queries in parallel, where n is the maximum depth, but it's tricky because you only know you're out of items at one level when you read an item that doesn't belong to the parent item, so you have a lot of remembering to do...

[[deleted]]

"...but they are spelled 'oracle' and 'db2'"

... and Informix 🙂

If you approach this the same way as a threaded forum, then you can use the 'threadid' or 'rootid' or 'grandparentid' to select all categories for the tree at once, and do the recursive work in PHP. That uses a little more memory, but saves tons of queries.