SQL запрос отбора связанных строк в одной таблице

Question

Есть одна таблица org, и столбцы id_org, name, parent_id. в столбце parent_id указывается id_org вышестоящей организации(подразделения). Как отобрать из таблицы все дочерние подразделения, например организации с id_org=1, а также дочерние строки дочерних строк.

id_org	name	parent_id
1	организация1	0
2	подразделение1-1	1
3	подразделение1-2	1
4	подразделение1-2-1	2
5	организация2	0
6	подразделение2-1	5
7	подразделение2-2	5

Запрос следующий запрос саму строку с id_org=1 не выводит, выводит дочерние подразделения только до первого уровня, т.е. дочерние дочерних не выводит, например не выводит "подразделение1-2-1", который является дочерним строке "подразделение1-1"

SELECT org1.*
FROM org AS org1
     JOIN org AS org2
       ON (org2.id_org = org1.parent_id)
WHERE org2.id_org = 1;

Answer 1

Посмотрите рекурсивный вариант решения по указанным выше ссылкам. Это органичный метод решения Вашей задачи.

Предложу пример решения через JOIN. Глубину можете увеличивать, добавляя ступени. Для ритмичности текста, сохранение предыдущих уровней сделал избыточным - убираем лишнее через DISTINCT. Выбираем от начального узла вниз ( к подчиненным записям)

select distinct id_org,name,parent_id
-- каждый join -- новый уровень в дереве
from( -- 5
select t2.id_org,t2.name,t2.parent_id 
from( -- 4
select t2.id_org,t2.name,t2.parent_id 
from( -- 3
select t2.id_org,t2.name,t2.parent_id 
from( -- 2
select t2.id_org,t2.name,t2.parent_id 
from( -- 1
      -- выборка начальных (начального) узлов
       select * from org where id_org=2 -- parent_id=0 
      )t1
left join org t2 on t2.parent_id=t1.id_org or t2.id_org=t1.id_org
)t1
left join org t2 on t2.parent_id=t1.id_org or t2.id_org=t1.id_org
)t1
left join org t2 on t2.parent_id=t1.id_org or t2.id_org=t1.id_org
)t1
left join org t2 on t2.parent_id=t1.id_org or t2.id_org=t1.id_org  
-- конец ступеней
)r

Answer 2

Подсказали, что можно так получить ответ

WITH RECURSIVE r (id, name, parent_id)
AS (
    SELECT id, name, parent_id
    FROM org
    WHERE id = 1

    UNION ALL

    SELECT org.id,
        org.name,
        org.parent_id
    FROM org, r
    WHERE (r.id = org.parent_id)
)
SELECT * FROM r;

в MS SQL Server 2019 пришлось убрать RECURSIVE

WITH r (id, name, parent_id)
    AS (
        SELECT id, name, parent_id
        FROM org
        WHERE id = 1
    
        UNION ALL
    
        SELECT org.id,
            org.name,
            org.parent_id
        FROM org, r
        WHERE (r.id = org.parent_id)
    )
    SELECT * FROM r;

Результат:

БЛОГ НА HUSL

SQL запрос отбора связанных строк в одной таблице

Ответы (2 шт):