Using a recursive CTE to read the root parent id of a hierarchical table
Note that this post could be considered the second part in my previously incredibly short series of 1 posts: Good Uses for SQL Server 2005 Common Table Expressions - previous entries being:
--
Ok, so the title of the post is a bit of a mouthful - sorry about that. I was asked this as a question today, and as it had been a little while since I had looked at CTEs I thought it would be a good exercise to sit down and work it out.
Consider the following table, called Amoeba. (Amoebas are useful in this context because they only have one parent, as far as I am aware)
AmoebaId | ParentAmoebaId |
---|---|
1 | NULL |
2 | 1 |
3 | 1 |
4 | 2 |
5 | 3 |
6 | NULL |
7 | 6 |
8 | 7 |
The amoebas with id 1 and 6 are “ultimate ancestors”, as they don’t have a parent. What we need to do is for any given an AmoebaId, find out the AmoebaId of its ultimate ancestor.
Doing this with a recursive CTE is actually pretty straight forward:
1
2DECLARE @CurrentAmoebaId int, @UltimateAncestorId int
3SET @CurrentAmoebaId = 5
4;WITH ParentAmoebas (AmoebaId, ParentAmoebaId)
5AS
6(
7 SELECT a.AmoebaId, a.ParentAmoebaId
8 FROM Amoebas a
9 WHERE p.AmoebaId = @CurrentAmoebaId
10 UNION ALL
11 -- Perform the recursive join
12 SELECT a.AmoebaId, a.ParentAmoebaId
13 FROM Amoebas a
14 INNER JOIN ParentAmoebas pa ON pa.ParentAmoebaId = a.AmoebaId
15)
16-- Grab the AmoebaId of the ultimate ancestor
17-- this will be the only entry with a null parent id
18SELECT @UltimateAncestorId = AmoebaId
19FROM ParentAmoebas
20WHERE ParentAmoebaId IS NULL
21SELECT @UltimateAncestorId
The inline comments should explain things in enough detail, but essentially the CTE will keep recursing up the page hierarchy, setting the @UltimateAmoebaId variable, until the amoeba without a amoeba id is found. At this point @UltimateAmoebaId will be set to the correct ultimate ancestor id.