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
1 Simon
2 Edward
3 Nigel
4 Geoff
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: . 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.