Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Self-referencing Hierarchy?

Status
Not open for further replies.

ajc123

Programmer
Oct 6, 2001
25
US
Somebody sent me lots of data in a self-referencing hierarchy. There's an "ID" field and a "Parent_ID" field. In another popular database, I would use a "Connect_By" clause. Is there a way to do this in SQL Server? Or has someone got some spiffy TSQL code to recurse through the hierarchy and give me all the ancestor generations?

Thanks,

Avery.
 
Assuming you want to show the parent for every child in your hierarchy, let's say your file looks like the following Employee table:

id int
parent_id int
employee char(50)

with data that looks like:

id parent_id employee
------------ ------------- --------------------
1 null Bill Gates
2 1 Steve Balmer
3 1 Ken Levy
4 2 Steve Jobs
5 3 Yehudi Menuhin


You would construct a query that looks like the following:

Select
Child.Employee as Employee,
Parent.Employee as Manager
from Employee Child (nolock)
left join Employee Parent (nolock) on Child.parent_id = Parent.id

This should render you with:

Employee Manager
------------------ -----------------
Bill Gates (Null)
Steve Balmer Bill Gates
Ken Levy Bill Gates
Steve Jobs Steve Balmer
Yehudi Menuhin Ken Levey
 
The issue here is that I have a mutiple-level hierarchy, and want to see all the relations.

I want to know that "Steve Jobs" ultimately is in the "Bill Gates" Hierarchy.


 
How do you want the data to display? In a tree view or hierarchical data grid, or what? Like the following I assume.

Bill Gates
Steve Balmer
Steve Jobs
Ken Levy
Yedudi Menuhin

The only way to get every "node" to walk back up through the tree would be to put everything in a cursor and then fetch the rows sequentially in a loop until Bill Gates (or God)was encountered. This way you could have unlimited branches. The whole key here is how you want the results to show. With any more than 3 levels, this would be a pita to code for a hierarchical datagrid. Its a lot easier to code for a treeview
 
I want to retrieve the maximum entitlement for an employee, which is inherited from his ancestor records. I need to select the max(value) from the set of all ancestors.

Looks like I need to use a Stored Procedure to do this. I have 100,000 records, so I was hoping to do it with a query.
 
You may still be able to perform what you need in a query if you don't need to do fetch looping. Can you pull some actual data from your table, show some rows in a reply, followed by what the desired output is and I'm sure there is a simple solution. SQL does recursion very well.
 
sounds like you need a UDF that returns a list of ID for all the parents up the chain for a given row. Terry has posted something about this resently. The UDF would recursively call itself until it hits a record with a parent of NULL.


 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top