Применить значение дочерней таблицы ко всем родительским / дочерним связям в основной таблице

#sql #sql-server #tsql #recursive-query

#sql #sql-сервер #tsql #рекурсивный запрос

Вопрос:

СПРАВОЧНАЯ ИНФОРМАЦИЯ:

У меня довольно сложный сценарий (по крайней мере, для меня), в котором я хочу вернуть данные из 2 таблиц, где одна из них содержит родительскую / дочернюю или иерархическую связь, и только на некоторых членов семейства могут быть ссылки из другой таблицы.

Предположим, у меня есть 3 таблицы со следующими данными:

ТАБЛИЦА 1 — Определяет родительскую / дочернюю связь с именем. Может быть несколько уровней, не только родительский и дочерний, но и полная иерархия

 Id | Table1Name | ParentId
---------------------------
1  | Root       | NULL
2  | Child1     | 1
3  | Chile2     | 1
4  | Root2      | NULL
5  | Child1-2   | 4
6  | Child2-2   | 4

ТАБЛИЦА 2 — дочерняя таблица ТАБЛИЦЫ 3 ниже. Также ссылается на ТАБЛИЦУ 1 выше

 Id | Table2Name | Table1Id | Table3Id
-------------------------------------------
20 | Test1      | 2        | 40
21 | Test2      | 2        | 40
22 | Test3      | 3        | 40
23 | Test4      | 3        | 40
24 | Test5      | 5        | 41
25 | Test6      | 5        | 41
26 | Test7      | 6        | 41
27 | Test8      | 6        | 41

ТАБЛИЦА 3 — Родительская таблица — Включена только в качестве ссылки для этого примера, не используется в запросе

 Id | Table3Name
-----------------
40 | Parent1
41 | Parent2

ПРОБЛЕМА:

Я пытаюсь разработать запрос, в котором будут перечислены ВСЕ строки из ТАБЛИЦЫ 1, но будет включен Table3Id, который связан со всеми записями для этого родительского / дочернего отношения. Итак, для этого примера я ожидаю результирующий набор, который выглядит следующим образом:

 Id | Table1Name | ParentId | Table3Id
--------------------------------------
1  | Root       | NULL     | 40
2  | Child1     | 1        | 40
3  | Chile2     | 1        | 40
4  | Root2      | NULL     | 41
5  | Child1-2   | 4        | 41
6  | Child2-2   | 4        | 41

Обратите внимание, что строки 1 и 4 выше не имеют прямого отношения к ТАБЛИЦЕ 2, но имеют косвенное отношение через строки (2 и 3) и (5 и 6) соответственно.

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

1. Что должно произойти, если у вас есть запись в Table2, говорящая, что Table3Id для записи 2 равен 40, а для записи 3 равен 41. Каким должен быть результат для родительского элемента 2 и 3, 1?

2. Это хороший вопрос, который я хотел задать в своем вопросе выше, но забыл. Хотя это не очевидно из приведенного выше, будет бизнес-логика для предотвращения такого сценария. Итак, для всех интенсивных целей предположим, что сценарий никогда не мог существовать.

Ответ №1:

Вы используете рекурсивный CTE, начиная со всех дочерних элементов, которые имеют совпадение в Table2, а затем продвигаетесь вверх по дереву.

 WITH recHierarchy AS
(
    SELECT t1.Id, t1.ParentId, t2.Table3Id
      FROM #table1 t1
     INNER
      JOIN #table2 t2
        ON t1.Id = t2.Table1Id

     UNION ALL

     SELECT parent.Id, parent.ParentId, child.Table3Id
       FROM #table1 parent
       JOIN recHierarchy child
         ON child.ParentId = parent.Id
)
SELECT DISTINCT Id, ParentId, Table3Id FROM recHierarchy

Это также сработает, если в Table1 есть иерархия, подобная:

 Id | Table1Name | ParentId
---------------------------
7  | Root3       | NULL
8  | Child3-1    | 7
9  | Chile3-1-1  | 8

И Table3Id определяется только при Id = 9.

Рабочий пример на dbfiddle.

1. Большое вам спасибо за этот ответ и рабочий пример. Это было именно то, что я искал! Я был близок к этому, но действовал наоборот, начиная с родительского элемента и пытаясь получить дочерние элементы, что не сработало. Еще раз спасибо!