Условно-рекурсивный запрос в SQL Server

Не рекурсивно, но дает ожидаемые результаты

SELECT Id, Setting, 'From Table 2' as Source
FROM TABLE2   

UNION ALL  

SELECT T1.Id,t2.Setting, 'Direct child via Table 1' as Source
FROM TABLE2 t2
INNER JOIN TABLE1 t1 ON t2.Id=t1.ParentId
WHERE T2.ID <> T1.ID

UNION ALL  

SELECT T1.Id,t2.Setting, 'Grand child via Table 1 2x' as Source
FROM TABLE2 t2
INNER JOIN TABLE1 t3 ON t2.Id=t3.ParentId
INNER JOIN TABLE1 t1 ON t3.Id=t1.ParentId
WHERE  t1.ID not in (Select ID FROM table2)

рабочий пример

Я добавил некоторые настройки в ваш пример, возможно, вы можете сделать что-то вроде этого:

;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. Я создаю hierarchy-CTE идентификаторов, беря все строки и затем переходя к их родительскому и родительскому идентификаторам. Столбец уровня отслеживает, где мы находимся в дереве.
2. Затем внутри settings-CTE мы присоединяемся к «таблице» настроек по значению идентификатора или родительского идентификатора, это позволяет нам получить все значения настроек в дереве.
3. ROW_NUMBER() OVER(PARTITION BY h.ID ORDER BY h.level, case when setting.Id = h.id then 0 else 1 end) создает рейтинг для настройки, поскольку нам нужен ближайший уровень, мы упорядочиваем его по h.level, а затем по тому, насколько он соответствует h.Id или нет.
4. Наконец, мы извлекаем все настройки, принадлежащие самому нижнему уровню, выполнив: WHERE s.sort = 1

Выход:

Я думаю, что у вас было концептуально обратное рекурсивное соединение, поскольку оно относится к родителю и дочернему элементу. Возможно, есть что-то более элегантное, но это соответствует пути, по которому вы шли:

with Base as (
    select t.Id, t.ParentId, t.ShortName, DTC.Setting 
    from Table1 as t left outer join Table2 as DTC on DTC.Id = t.id
), Settings as (
    select Id, ShortName, Setting,
        case when setting is not null then Id end as RootId,
        case when setting is null then ParentId end as ParentId
    from Base
    union all
    select s.Id, s.ShortName, b.Setting,
        case when b.setting is not null then b.Id end,
        case when b.setting is null then b.ParentId end
    from Settings s inner join Base b on b.Id = s.ParentId
)
select Id, ShortName, Setting, RootId from Settings
where Setting is not null;

https://dbfiddle.uk/SIkgyCsO

При создании списка всех настроек на всех уровнях, возможно, имеет смысл пройти по дереву в другом направлении, а затем просто распространить настройку вниз. Я предполагаю, что типичное использование этого будет от известной отправной точки, работающей в обратном направлении.