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
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
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.