Recursive queries with SQL Server 2000

By: Greg Robidoux   |   Read Comments   |   Related Tips: More > Scripts

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

Employee
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
1	Bob	Jones	NULL	NULL	NULL
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?

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

• http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

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.

• http://support.microsoft.com/default.aspx?scid=kb;en-us;q248915
• http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
• http://www.sqlteam.com/item.asp?ItemID=8866
• http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro03/html/sql03b8.asp
• http://www.sqlteam.com/item.asp?ItemID=1602

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 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 post to learn more about CTEs and look for a future tip on how to use CTEs with SQL Server 2005.

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

Last Update: 8/30/2006

About the author

Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips

We Recommend

• More Database Developer Tips...

Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen." NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now Is “blocking” a bad word at your company? Contact the Edgewood SQL Server Consultants for the resolution. Solving SQL Server problems for millions of DBAs and Devs since 2006. Join now. Optimizing SQL Server performance can be a daunting task. Or is it?