Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Recursive queries with SQL Server 2000

MSSQLTips author Greg Robidoux By:   |   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.

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


Last Update: 8/30/2006


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

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
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
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.