Tree Hierarchy in SQL 2000
-
I have two tables as below.
Emp_No Name Designation SupervisorCode 01 Joe Sr. Acct VP-Finance 02 Robert VP-Acct CEO 03 Emily Mgr L1 Director_finance 04 Kim Asst AsstDir_Finance 06 Rose Asst AsstDir_Finance 1. Employee Table
Supervisor_code Emp_No Name Designation SupervisorCode Director_finance 01 Joe Sr. Acct VP-Finance VP-Finance 02 Robert VP-Acct CEO AsstDir_Finance 03 Emily Mgr L1 Director_finance 2. Supervisor Table
What I need procedure/query to display records in the Hierarchy for
- selected employee and people reporting to him/her.
- Then recursively display employees who might be reporting to the employee lised in previous step and so on..
Any thoughts.
Thanks in advance.
Techbrain
-
Depends on what output you want. To do extract of this type, I use temp tables using the logic as follows (create temp tables with structure required)
Insert into Temp1 from Supervisor where Emp_No = [start employee]
Insert Temp1 into Temp2
While [Temp2 contains rows(s)]
Begin
Delete contents of Temp3
Insert into Temp3 from Supervisor where [SupervisorCode] = temp2.[Designation]
Insert Temp3 into Temp1
Delete contents of Temp2
Insert Temp3 into Temp2
End
Far away is close at hand in the images of elsewhere.
Anon. -
See, if this helps:
http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
Google and you'll find numerous threads on this topic. Each serious book contains a least one chapter and Joe Celko did a whole book on this.
...btw, this is no assigned homework, is it?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply