Conditional recursive query in SQL Server

Question

I have these 2 tables:

TABLE 1

Id	Shortname	ParentId
C1	Child1	P1
P1	Parent1	GP1
GP1	GrandParent1	NULL
C2	Child2	P2
P2	Parent2	GP1

TABLE 2

Id	Setting
GP1	5
C2	1

The logic is the following: if we don't find a value for the setting in table 2, we are going up the hierarchy defined in table 1 until we find something.

In the example:

• if I want the setting for item C2 I will retrieve 1 (direct value of C2)

• if I want the setting for item C1 I will retrieve 5 (I have nothing in table 2, I'll look to its parent -P1 regarding table 1-, still nothing, I'll look to its parent -GP1-)

• if I want the setting for item P2,I will retrieve 5 (Parent is GP1)

I have tried to implement a recursive query but without success for the moment, I need help.

Thanks

So far now, the best I have:

with settings as
(
    select N.Id,N.ParentId, N.ShortName,DTC.Setting 
    from Table1 as N 
    left join Table2 as DTC on DTC.Id=N.id
)
, RecursiveTable AS (
    SELECT no.Id,ShortName,ParentId,Setting 
    FROM settings as no
    UNION ALL
    SELECT n.Id,n.ShortName, n.ParentId,n.Setting 
    FROM settings n

    INNER JOIN RecursiveTable rn ON n.id = rn.ParentId
)
select * from RecursiveTable

Answer 1

I added some settings to your example, you can do something like this perhaps:

;WITH Table1 AS(
SELECT  *
FROM    (
    VALUES  (N'C1', N'Child1', N'P1')
    ,   (N'P1', N'Parent1', N'GP1')
    ,   (N'GP1', N'GrandParent1', NULL)
    ,   (N'C2', N'Child2', N'P2')
    ,   (N'P2', N'Parent2', N'GP1')
) t (Id,Shortname,ParentId)
)
, table2 AS (
SELECT  *
FROM    (
    VALUES  (N'GP1', 5)
    ,   (N'C2', 1)
    ,   (N'C1', 10)
) t (Id,Setting)
)
, hierarchy AS (
    SELECT  id, shortname, parentid, 1 AS level
    FROM    table1 t
    
    UNION ALL
    SELECT  h.id, h.shortname, t.parentid, h.level + 1
    FROM    hierarchy h
    INNER JOIN table1 t
        ON  t.ID = h.ParentID
    )
, settings AS (
    SELECT  h.ID, h.shortname, setting.ID AS SettingID, setting.Setting
    ,   ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY h.level, case when setting.Id = h.id then 0 else 1 end) AS sort
    FROM    hierarchy h
    INNER JOIN table2 setting
        ON  setting.Id IN (h.id, h.parentid)
    )
SELECT  *
FROM    settings s
WHERE   s.sort = 1

1. I create a hierarchy-CTE of IDs by taking all rows and then going up to their parent and parent's parent ID. A level-column keeps track of where we are in the tree
2. Then, inside the settings-CTE, we join on the settings "table" by either ID or parentID value, this allows us to fetch all the setting values in the tree.
3. ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY h.level, case when setting.Id = h.id then 0 else 1 end) creates a ranking for the setting, since we want it from the closest level, we order it by h.level and then by how it matches against the h.Id or not.
4. Finally, we retrieve all the settings belonging on the lowest level by doing: WHERE s.sort = 1

Output:

ID	shortname	SettingID	Setting	sort
C1	Child1	C1	10	1
C2	Child2	C2	1	1
GP1	GrandParent1	GP1	5	1
P1	Parent1	GP1	5	1
P2	Parent2	GP1	5	1