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)
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.