# Help on Self-Referential CTE SQL SERVER

I have this table below which is has a hierarchy to it where the number of levels vary, for this example I chose a subset with 7 levels.

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     NULL
4505    4485    3     NULL
5066    4505    4     NULL
6121    5066    5     NULL
5068    6121    6     NULL


My goal is to grab the property of the Parent that isn't NULL. So in this case, this should be returned:

L_Key   Parent  Level P_Key
-------------------------
1393    NULL    0     2
1399    1393    1     325
4485    1399    2     325
4505    4485    3     325
5066    4505    4     325
6121    5066    5     325
5068    6121    6     325


This is what i have tried so far.

;WITH CTE AS (
SELECT
m.Functional_Location_Key
,m.Parent_Key
,m.Level
,m.Property_Key
FROM #MappingPartOne m
WHERE m.Functional_Location_Key IN (6121,5068,5066,4505,4485,1399,1393)

UNION ALL

SELECT
t.Functional_Location_Key
,t.Parent_Key
,t.Level
,t.Property_Key
FROM CTE t
JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
Functional_Location_Key
,Parent_Key
,Level
,Property_Key
FROM CTE
option (maxrecursion 0)


Except this causes an infinite loop.

I have setup a table variable for your testing

DECLARE @MappingPartOne TABLE (
Functional_Location_Key int,
Parent_Key int,
Level int,
Property_Key int
)
INSERT INTO @MappingPartOne VALUES (1393,   NULL,   0,  2)
INSERT INTO @MappingPartOne VALUES (1399,   1393,   1,  325)
INSERT INTO @MappingPartOne VALUES (4485,   1399,   2,  NULL)
INSERT INTO @MappingPartOne VALUES (4505,   4485,   3,  NULL)
INSERT INTO @MappingPartOne VALUES (5066,   4505,   4,  NULL)
INSERT INTO @MappingPartOne VALUES (6121,   5066,   5,  NULL)
INSERT INTO @MappingPartOne VALUES (5068,   6121,   6,  NULL)

• Clarification: Do you mean that, when a given row's Property_Key is NULL, it should use the Property_Key of its nearest ancestor with a non-NULL Property_Key? Or, that it should use the Property_Key of the earliest ancestor with a non-NULL Property_Key? For example: If the row with the ID 4505 had the Property_Key 9978 instead of NULL, should its descendants (5066, 6121, and 5068) display Property_Key 9978, or 325? – RDFozz Oct 27 '17 at 22:44
• If the row ID 4505 has Property_Key 9978 instead of NULL, then its descendants would display 325. Thanks – Kevin Nguyen Oct 30 '17 at 14:14
• So, why 325 instead of 2 (Property_Key from the actual top parent record)? Is that first one supposed to be ignored for some reason? The natural pattern would assign 2 to all NULLs, not 325. – RDFozz Oct 30 '17 at 15:32
• FYI - updated my answer to cover what you're asking for from your last comment; include how to use actual highest ancestor's value as default, instead of second-highest ancestor. – RDFozz Oct 30 '17 at 16:54
• My apologies, I did not read your comment correctly. What you had as the first answer was correct. However, I am sure your updated answer will help someone else in the future. I was supposed to say that the descendants would have Property_Key 9978. – Kevin Nguyen Oct 31 '17 at 13:07

Your query is running an infinite loop because it always finds more records that can be added. That's because the rows you're adding in the second half of the query (after the UNION ALL) are the exact rows that are in the first half of the query. You won't get an infinite loop if you use the columns from the @MappingPartOne table, not CTE, in the SELECT list. You won't get what you want but you won't get an infinite loop.

If I'm understanding you correctly, when a row's Property_Key is NULL, you want to use the same Property_Key value as used by the row's parent - in other words, the nearest ancestor's Property_Key where that value is not NULL. For example: if the row with Functional_Location_Key = 4505 had Property_Key = 9978 instead of NULL, its descendants (Functional_Location_Key 5066, 6121, and 5068) would display Property_Key = 9978.

We need to make several changes to your query to accomplish this:

• First, as previously noted, the second part of the CTE should output the columns from the @MappingPartOne table, not from CTE. This, however, gives you multiple copies of your rows.

• To get a single copy of each of your rows, you need to start the CTE differently. You're initially explicitly providing a list of the rows you want to see in the first part of the CTE. However, then, in the second part, you're bringing in the descendants of each row from the previous iteration. Since you've included rows in the first part that are the descendants of other rows you've included, you get duplicates as you iterate through the rows.

Instead, you need to specify just the ultimate parent rows - rows that aren't descendants of any other rows. That means, the rows (in the present case, row) where Parent_Key is NULL.

• Now, the next step is to check if a row's Property_Key is NULL, and (if so) to use the same value as its parent did. It doesn't matter if the parent's Property_Key was also NULL, because then it would be using its parent's Property_Key. During each iteration through our data, we have the current row (from @MappingPartOne) and its parent (from CTE). So, instead of simply using the @MappingPartOne.Property_Key, we check if that's NULL. If it's not, we use it - if it is, we use CTE.Property_Key.

So, this gives us the following query:

WITH CTE AS (
SELECT
m.Functional_Location_Key
,m.Parent_Key
,m.Level
,m.Property_Key
FROM #MappingPartOne m
WHERE m.Parent_Key IS NULL

UNION ALL

SELECT
b.Functional_Location_Key
,b.Parent_Key
,b.Level
,COALESCE(b.Property_Key, t.Property_Key) as Property_Key
FROM CTE t
JOIN #MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
Functional_Location_Key
,Parent_Key
,Level
,Property_Key
FROM CTE
;


You can see this in action at this SQLFiddle link.

(If you haven't used it before, COALESCE() works like the ISNULL() function. It has two potential advantages: you can provide more than two arguments, in which case it will return the first non-NULL argument, or NULL if all of them are NULL; and, it's part of the ANSI standard, and thus more likely to be portable to other SQL dialects.)

If you change the WHERE clause criteria in the first part of the CTE to pick a specific row by Functional_Location_Key, then you can pull up any given row and its descendants, ignoring the rest of the hierarchy, in case that's needed.

Per your comments, you seem to want to show the Property_Key from the highest level ancestor with a parent (not from roe 1393, but from row 1399) as the default for all its descendants with a NULL Property_Key.

To do this, we need only need to introduce an additional column to our CTE:

WITH CTE AS (
SELECT
m.Functional_Location_Key
,m.Parent_Key
,m.Level
,m.Property_Key
,NULL as Top_Property_Key
FROM MappingPartOne m
WHERE m.Parent_Key IS NULL

UNION ALL

SELECT
b.Functional_Location_Key
,b.Parent_Key
,b.Level
,COALESCE(b.Property_Key, t.Top_Property_Key) as Property_Key
,COALESCE(t.Top_Property_Key, b.Property_Key) as Top_Property_Key
FROM CTE t
JOIN MappingPartOne b ON b.Parent_Key = t.Functional_Location_Key
)
SELECT
Functional_Location_Key
,Parent_Key
,Level
,Property_Key
FROM CTE
;


The new column is Top_Property_Key. Initially, we're looking at the top level parent. You've said we don't want to use that as our default, so we set the value for this one row to NULL. In the second part of the CTE, we want to use the established Top_Property_Key as our default if it's not currently NULL, otherwise we want to use the current parent row's Property_Key. Once we have a row where Top_Property_Key is assigned a non-NULL value, it will retain that value for all its children.

Finally, we change the COALESCE for each row's Property_Key to use our new column, t.Top_Property_Key, instead of t.Property_Key (the immediate parent's property key value).

Here's an updated SQLFiddle link with the new functionality.

NOTE: If you decide you want to use the very top parent's Property_Key as the default for all its children, then set Top_Property_Key to Property_Key instead of NULL in the first part of the CTE:

        ,m.Property_Key as Top_Property_Key

• Seeing we both ended up in the same place makes me feel a lot better, hahaha – Erik Darling Oct 28 '17 at 0:52
• Thank you for the clear explanations, seems like i was missing 2 key parts. Starting at the Parent (where Parent_Key is NULL) and the COALESCE – Kevin Nguyen Oct 30 '17 at 14:15