Retrieving Parents from self Referencing table | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Retrieving Parents from self Referencing table


I Got the trouble in retriving the parent records in Self referencing table table structure is as follows
EMPID EMPNAME
REPORTTO
1 Simon
2 Edward
1
3 Nigel
1
4 Geoff
2
5 Gerald
4 Here ReportTo column references the Empid column of the same table. I need Sql query to retrive all the parents of Employee Gerald Output should be like this 1 Simon
2 EDward
4 Geoff
5 Gerald Any one has the answer for this? Srinivas
In order to do this with your current structure you will need to use a cursor. If you can modify your table structure, or add a related table to handle the hierarchy, I would suggest looking at this article:http://www.intelligententerprise.com/001020/celko1_1.shtml . The nested sets model that Joe Celko talks about is far superior to the adjacency list model you are currently using. While it may look a little daunting at first, this is the best way I have found to represent a tree in SQL, and is not really that hard to master.
]]>