Building a Tree Structure using Pro*C

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Building a Tree Structure using Pro*C

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-24 : 07:45:29

saravanane writes "Hi groups,

I have a table named OfficeHierarchy where officeid and HeadOfficeid as columns and officeId is a Primary Key for that table.

my table structure looks like this

---------------------------------
officeId | headofficeid |
----------------------------------
chennai | bangalore |
_________________________________
Bangalore | Delhi |
__________________________________
Delhi | London |
_________________________________
London | France |
----------------------------------

Now i want my SQL query or a stored procedure should spot out all the Headoffice id's recursively.

for an example,

If my city is Bangalore ===> I should retrive its own HeadofficeId (Delhi) and inturn it should again retrive Delhi's
headofficeId (London) and inturn it should again retrive London's
HeadofficeId (france) and France is the main officeid and it will not be present in the column officeid

the query should result (for bangalore)
* Delhi
* London
* france

thank you in advance
It's urgent Kindly Help me....

E.saravanan
Programmer
IndigoTx"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-24 : 08:04:28

There is a decent bit of info about trees here: http://www.seventhnight.com/treestructs.asp

Corey