Recursive queries with SQL Server 2000

By:   |   Updated: 2006-08-30   |   Comments   |   Related: More > Scripts


In SQL Server 2000 there is no simple way to create recursive queries that have several levels of data (hierarchical data).  Generally a recursive query is needed when you have a parent and child data stored in the same table.  One example may be employee data where all employee data is stored in one Employee table and there is an indicator that specifies the employees supervisor that points back to another record in the same table.  This is done quite often for other data as well. 

The following shows a sample table and data.

EmpID FirstName LastName SupervisorID
1 Bob Jones NULL
2 Gina Smith 1
3 Tim Owens 1
4 Mary Jacobs 3

To query this data to find the employee and supervisor is pretty easy.  The query would be:

SELECT e1.EmpId, e1.FirstName, e1.LastName, e1.SupervisorID, e2.FirstName AS SupFirstName, e2.LastName AS SupLastName
FROM Employee e1 LEFT JOIN Employee e2 ON e1.SupervisorID = e2.EmpId

When we run this query these are the results we get:

EmpID FirstName LastName SupervisorID SupFirstName SupLastName
2 Gina Smith 1 Bob Jones
3 Tim Owens 1 Bob Jones
4 Mary Jacobs 4 Tim Owens

So you can see this is pretty simple to just get this level of data, but what if you want to find out all of the direct and indirect reports of Bob Jones.  Well this now becomes a bit more difficult. In this example there are only 3 levels of data, but what if you have a much larger hierarchy?


As mentioned above there is no simple way to produce recursive queries that have several levels of data, but several implementations have been produced and are available on the internet.  Finding all of these options, understanding them, implementing and testing takes some time, so to make your life a little easier we have gathered a list of some useful variations on how to produce recursive queries in SQL Server 2000.

The following solution is pretty straight forward and offers a lot of flexibility.  Although it met my needs, take a look at the other options as well to see which one makes the most sense for your needs.

This is a list of various implementations and methods for creating recursive queries for SQL Server 2000. There are probably others out there as well.

With all of these options the parentID is found and then there is a link to the child data and then that child's data etc...  One of the things that these articles also specify is creating a lineage of what records belong to which parent, grand-parent, etc...  This lineage then gets stored with the data so that when you are searching for records you can use the LIKE clause in your WHERE statement to find any records that have this value. 

Overall there is not a simple clean approach for dealing with this data, but using one or a combination of these techniques should allow you to reach your goal.

Note: With SQL Server 2005 and later you have the option of creating recursive queries using Common Table Expressions or CTEs. With CTEs, Microsoft has made this process a little easier then in previous versions of SQL Server.  Take a look at this tip to learn more about CTEs.

Next Steps
  • Take a look at these various methods for creating recursive queries
  • Select a method that works best for your environment
  • Modify these methods to implement the right solution for your needs

get scripts

next tip button

About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of

View all my tips

Article Last Updated: 2006-08-30

Comments For This Article


get free sql tips
agree to terms