Multiple CTE’s in a Query – Demo

As a continuation from the day before, here is the multiple CTE’s in a query demo.

Transcription of the demo:

All right. So here’s an example of a simple CTE that just does a union to return a list that says John, Mary, and Bill. And then we have a second one, that’s last names that just returns, Smith give and Jones. And then we use cross join. And what it does, it combines all the first names with all the last names, and makes up a list of three first names combined with all three last names, or nine total names, John Smith, Mary Smith, Bill Smith, John, good, Mary, get Bill Gibb, and John Jones, Mary Jones and build jobs. That’s useful if you need to populate a database with a whole bunch of test data, for instance, for load testing, or performance testing. All right, let’s take a look at the demo code for this. Here’s an example of an inner query, we’re just going to run that inner query and see what it returns select ID department and parent from departments where the department ID equals four, we run that department for his fitness. And this is an example of how to do things without the CTE.

And then we’ll run that second one where we select ID department and parent from departments that returns everything. And then we’re joining on the idea of one equals the parent on two. But since q1 was only filtered to fitness, it’s going to just return the fitness department with all the fitness sub departments. Let’s take a look at how do we do that with a CTE? Well, similar, we start out with the inner query, select ID department and parent from departments where the ID equals four. That gives us fitness. Then we can select the ID, department id parent from department, so we’ll just test that piece. That gives us the whole list. And then we do the same inner join. And we can join the two of them together. And we get the top level of fitness with all the sub departments of yoga, running, and swimming listed there as well.

Let’s take a look at the names example that we saw from the slide we saw a minute ago. Here we’re going to select John Mary and Bill using union statements just build us a list of three first names. Now imagine if you’ve done that with 21st names instead of just three. And then what we’re going to do is we’re going to wrap that in a CTE where we’re just selecting the names out bill, John and Mary. Not that exciting yet. What were they going to then do is declare the F names for first names CTE. As John Mary and Bill, we’re going to declare the last names as Smith Gibbon Jones. And then we’re going to select the first name and the last name, and we’re going to cross join, and that should create the nine names that I talked about in the slide. And there you have Bill, give John give Mary give Bill Jones, John Jones, Mary Jones and Bill Smith, John Smith, Mary Smith. Now if you want to make it more interesting, we can throw in some middle names, and reformat things a little bit. So the F names is the same, the L names are the same. And the M initials has been added as another CTE where we just added a comma, gave it the name, declared the column and put a query in. And then we referenced that down here. And what that’s going to do is take the nine names that we had and give them all middle initials of A, B or C and take that nine to 27 rows. And you can see there’s the 27 rows.

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!