?
Solved

Need help on understanding recursive CTE

Posted on 2013-01-17
7
Medium Priority
?
356 Views
Last Modified: 2013-01-17
Hello,
I am studying recursive CTE and I am having trouble understanding the recursive portion of the cte.  Below is the example.  I want to make sure that I understand what's going on.  I understand the anchor member portion but am not sure how the recursive portion works.  I've read the explanation but am still having trouble with the concept.  I'm studying on my own.

USE Examples;

WITH EmployeesCTE AS
(
		-- Anchor member
		SELECT EmployeeID, 
			FirstName + ' ' + LastName As EmployeeName, 
			1 As Rank
		FROM Employees
		WHERE ManagerID IS NULL
	UNION ALL
		-- Recursive member
		SELECT Employees.EmployeeID, 
			FirstName + ' ' + LastName, 
			Rank + 1
		FROM Employees
			JOIN EmployeesCTE
			ON Employees.ManagerID = EmployeesCTE.EmployeeID
)
SELECT *
FROM EmployeesCTE
ORDER BY Rank, EmployeeID

Open in new window

0
Comment
Question by:chtullu135
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 

Author Comment

by:chtullu135
ID: 38788649
I think what is happening is that "Join EmployeesCTE" is referring to the previous pass .
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 38788695
Hit chtullu135,

There are a couple of articles on the DB2 forum that explain recursive SQL very well.  The only difference between the DB2 and SQL Server variants is how string parameters are sized (it's explained in the articles).  The only other significant difference is that DB2 uses the double pipe (||) for string concatenation and SQL Server uses the plus sign (+).

  http://www.experts-exchange.com/Database/DB2/A_3629-Recursive-SQL-in-DB2-Converting-Columns-to-Rows.html

  http://www.experts-exchange.com/Database/DB2/A_3618-Recursive-SQL-in-DB2-Converting-rows-to-columns.html


Briefly, the upper sub-query in the CTE establishes the starting point of the recursive query.  The lower sub-query in the CTE processes all of the rows (except the first row) and defines when the recursion is complete.


In your query, the upper sub-query selects all employees without a manager.  The lower sub-query doesn't seem to select rows in a meaningful fashion.  What is it trying to do?


Kent
0
 

Author Comment

by:chtullu135
ID: 38788822
Hello Kent,
The lower subquery returns each employee according the their level in the organization.  Below is the list of Employee

Empid                                             Managerid
1                Smith      Cindy               NULL
2                Jones      Elmer                 1
3                Simonian      Ralph         2
4               Hernandez      Olivia         9
5               Aaronsen      Robert         4
6               Watson      Denise                 8
7                Hardy      Thomas                2
8      O'Leary      Rhea      9
9      Locario      Paulo      1
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 46

Accepted Solution

by:
Kent Olsen earned 2000 total points
ID: 38788905
Ok.

Using your sample data, the upper sub-query selects the row for Cindy Smith -- the only row with NULL for the manager id.

The lower sub-query takes that row and joins the employees table to it and filters in a way that the employees that are Cindy's direct reports are selected.  (Paulo Locario and Elmer Jones.)

The next iteration selects the rows for people that report to Locario and Jones.

Note that the selections are not linear.  People want to think of recursive SQL as iterating through a list.  In reality, SQL is taking some number of rows of data and generating more rows of data.  (All level 1 rows are generated at the same time, all level 2 rows are generated at the same time, etc.)

The other real gotcha is that ALL of the rows that are generated are part of the final result set.  You may need to filter or sort them differently for your own needs.  In this case, I believe that you'd be well served for the DTE to pass the employeeID and managerID values.


Kent
0
 

Author Comment

by:chtullu135
ID: 38788927
Thanks Kent.  My problem was that I was thinking the the recursion was iterating through a list instead of a set of rows.
0
 

Author Closing Comment

by:chtullu135
ID: 38791822
Thanks again for your help
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question