i have a self joined table what is the best way to rebuild the tree up

like

1 Country null 2 City 1 3 Neighborhood 2

i need to generate this string tracing the tree up

Neighborhood > City > Country

with minimum Queries as it's going to be intensely used across the website

bo2bo2 wrote:

i have a self joined table what is the best way to rebuild the tree up

like

1 Country null 2 City 1 3 Neighborhood 2

i need to generate this string tracing the tree up

Neighborhood > City > Country

with minimum Queries as it's going to be intensely used across the website

Can't you just cache this information (you're on the neighborhood page, then move on to the city page, and because you cached the neighborhood value in the session for example, you don't have to read that again)? Also, you just store strings in a single table? Because a city isn't a country...

actually it's a locations table and the first line was a record not the table headers cuz i have like infinite growing tree i can't keep it cached it's a community website i can do this through T-SQL but i am trying to minimize dependance on SQL server incase we have to move

so what i need is an easy way to build the this tree is which way is the best.

and we'll probably revisit it later for performance optimization

You might consider fetching the entire table in 1 query and then build the tree in memory using a hashtable with treenodes.

Check this out: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=8706

Representing tree in sql is something that can be heavy. Some year ago I've found on the net the nested set model and since this time I only use this model to represent a hierarchical tree.

You should take a look on this model, i.e. for your problem it's only one query to get all the path to your node. Search on the net, maybe start with these links: http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=235427 http://www.developer.com/db/article.php/3517366

yep ok i've figured out a way to cache it dynmically any way thanks