Self-referencing Hierarchy?

[ajc123]

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.

 

[Bobalooey]

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

 

[ajc123]

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.

 

[Bobalooey]

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

 

[ajc123]

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.

 

[Bobalooey]

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.

 

[SemperFiDownUnda]

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.

 

[alfredjp]

a good discussion/discourse about using recursive algorithms for heirarchical data (in sql server) has been presented at msdn at this url:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/recursion.asp

and as you'll note - some sample codes are also available...

cheers...