Steve Explains how to build a Recursive CTE

Transcription of Video:

So here’s an example of building a recursive common table expression, I’m gonna start out with the with statement, we’re gonna call it department CTE. And we’re going to label the columns that we’re going to return department ID, the department, the parent, and those are all things we’ve seen before earlier in the presentation. And then we’re going to be asking for the level and the level is going to be the depth of recursion. So all the departments at the top level will be level zero, then all the departments at the level below that will be level one. And for sub departments below that there’ll be level 234, or deeper, depending on how far we go. Then we add the anchor query. And this is basically just saying, Give me all the departments where the parent is no, which means give me all the top level departments. And setting zero is the level on those just hard coding that as a starting point, UNION all that’s kind of a magic keyword inside of the CTE that says take that anchor query that’s above and connect it up to the recursive query. And here, the recursive query is shown in red, where we’re saying SELECT the department ID, department and parent from departments. But we’re joining back to the department CTE, which is the CTE we’re building right now. And we’re joining it on the department ID, which equals the parent in departments. And then we’re grabbing the level from the department CTE and adding one to it. And that’s how we’re saying that first level of recursion will be zero plus one or level one, the second level will be zero plus one plus one, or level two. And then the last thing we do is we just SELECT from that CTE ordering by some value we want to order by.

Alright, let’s take a look at the demo and see exactly how that works. So we’re going to take a look at our departments table again, just like it’s looked all the way through the demo. 17 departments there, some of them have parents defined, some of them don’t. And then we’re going to find all the top level departments. So that’s where the parent is null. And here we get for top level departments, campaign cycle, snow, sports and fitness.

Now, a non CTE way to do this would just be to take departments and join it to departments, again, on the parent. And when we run that, we’d be able to get the top level and the child level and be able to build a two level hierarchy there. But it gets really unwieldy when you get to the point of more than two levels or three levels there. Another way is you could do this and set the level here, similar to what we talked about in the CTE. But these are really only good when there’s not a lot of depth into the department’s there.

Okay, now let’s take a look at building the anchor query. So here, the anchor query we’re going to start with is just give me all the departments that are at the top level where the parent is now. And we’re going to set the level and this was a typo, I need to fix. And we’re going to set the level to zero as the starting point. So all the top level will be level zero, then we’re going to wrap that in a CTE. So we’re just going to call this department CTE, we’re going to put our anchor query in there. And we’re going to SELECT everything out of that. And like we talked about earlier on some of the simple CTE is not very exciting, yet. So all we’re doing is getting the top levels that have been defined as level zero, you can do all of that with a non CTE type query. But here’s where it’s going to get interesting. Now, what we need to do is to go find those departments that are not at the top level, or those that are sub departments or the other departments. And the way we’re going to do that. And this is going to be the recursive part of it, we’re going to say, give me the ID, department and parent, we’re going to add one to the previous level from departments. And then we’re going to enter join back to the CTE itself. linking up the parent from the departments, to the ID that’s defined in the CTE, which would initially be the top level or it would be the level prior to this level. But we can’t run that because that department CT doesn’t exist yet. So what we do is we declare the CTE we have the anchor query, we drop in the UNION all, which is how we say run this top thing, first, run the anchor query first. And then for all the results that come out of that, run this next query. And for all the results that come out of that. repeatedly run this next query until there’s no more results, or until our recursion level is, is hit. So here we’re going to say SELECT star from departments CTE, we get all of our departments listed and the level they’re at.

Okay, but let’s make it more interesting. Let’s run this procedure I created earlier which fills in more departments. And when we look at the non CTE query that really gets thrown off here, because there’s all this stuff that has a parent, but it’s not at level zero or one. So what we’re gonna do is we’ll run the CTE again and take a look at it. And we can see, here’s all the level zero items. Here’s all the level one items. Here’s all the level two items, which is new, this one person, two person, three person and four person are a child of item five. Item five is tents. So this is one person tent. And then under. Here, we have backpacking, family camping, mountaineering, ultra light, or mountaineering, and those are a child of 19. So those are two person tents, and then ultra lightweight, lightweight and standard which are a child of department 24, which is backpacking. So this standard would be a standard backpacking, two person tent that’s in the camping department.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!