Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
547 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
No blog on CTEs would be complete without mentioning recursive CTEs. These are complicated, and for 90% of SQL programmers unnecessary, so we will consider first when you might need these beasts.
Recursive CTEs are ideal when you store data in a hierarchy. There are two typical scenarios:
Wherever you have a hierarchical structure stored like this, a recursive CTE is the way to get at your data!
We'll use an example of a menu taken from a carnival website:
Each menu contains a ParentMenuId field, giving its immediate parent
Consider the highlighted menu. This is the Senior menu, which belongs in menu number 5 (Fell race), which in turn belongs in menu number 2 (Carnival). This gives a breadcrumb as follows:
Top > Carnival > Fell race > Senior
Here's a query which would show these breadcrumbs for each menu:
WITH menus AS (
-- get the first row (ie the menu whose parent is 0)
SELECT
MenuId,
Caption,
CAST('Top' AS varchar(100)) AS Breadcrumb
FROM
tblMenu
WHERE
ParentMenuId = 0
-- linked to all of those rows whose parent menu id
-- equals this row's menu id
UNION ALL
SELECT
submenus.MenuId,
submenus.Caption,
CAST((m.Breadcrumb + ' > ' + m.Caption) AS varchar(100)) AS Breadcrumb
FROM
tblMenu AS submenus
INNER JOIN menus AS m
ON submenus.ParentMenuId = m.MenuId
)
-- the maximum recursions are set to 4 (this
-- guards against recursive CTEs with bugs in which
-- never finish)
SELECT
MenuId,
Caption,
Breadcrumb + ' < ' + Caption AS Breadcrumbs
FROM
menus
OPTION (MAXRECURSION 4)
Here's the output from this query:
For the Senior menu, for example, you can see the full menu path
To be honest, I struggle to get my head round this - it's one of those queries you can copy and adapt without ever fully understanding. But here goes ...
The first thing we do is to get a record for the top item in the menu:
-- get the first row (ie the menu whose parent is 0)
SELECT
MenuId,
Caption,
CAST('Top' AS varchar(100)) AS Breadcrumb
FROM
tblMenu
WHERE
ParentMenuId = 0
This would produce the following output:
This query shows the single menu which doesn't have a parent
The next thing to do is to show as part of the same set of results (using UNION ALL) the breadcrumbs for the next level down of menus (ie those whose parent menu is number 2):
-- linked to all of those rows whose parent menu id
-- equals this row's menu id
UNION ALL
SELECT
submenus.MenuId,
submenus.Caption,
CAST((m.Breadcrumb + ' > ' + m.Caption) AS varchar(100)) AS Breadcrumb
FROM
tblMenu AS submenus
INNER JOIN menus AS m
ON submenus.ParentMenuId = m.MenuId
After the first iteration, this would give:
After one iteration, we get the top menu plus all of its immediate children.
The clever thing about recursive CTEs is that they will then repeat this process to get all of these children's children, and all of their children, etc.
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.