Получить родительский учетный запись sql server 2008

У меня есть таблица COMPANY, как показано ниже.

CompanyID CompanyName

 1 xyz
 2 xyz-c
 3 xyz-c1
 4 xyz-c2
 5 xyz-c-c
 6 xyz-c-c1
 7 xyz-c-c2
 8 xyz-c-c1-c
 9 xyz-c-c1-c1
10 xyz-c-c1-c2

и у меня есть таблица COMPANYMAPPING, как показано ниже

CompanyMapID ParentCompanyID ChildCompanyID
 1 1 2 
 2 1 3 
 3 1 4 
 4 2 5 
 5 2 6 
 6 2 7 
 7 6 8 
 8 6 9 
 9 6 10 
 10 11 12

Я хочу получить дочерние корпоративные записи каждой компании, используя выше 2 таблицы, именно я хочу получить результат, как показано ниже

CompanyID CompanyName Level
 1 xyz 0
 2 xyz-c 1
 3 xyz-c1 1
 4 xyz-c2 1
 5 xyz-c-c 2
 6 xyz-c-c1 2
 7 xyz-c-c2 2
 8 xyz-c-c1-c 3
 9 xyz-c-c1-c1 3
10 xyz-c-c1-c2 3

я прошел через рекурсивный CTE, но не вписываясь в это, у меня есть несколько наборов родительских дочерних (нескольких уровней) записей, поэтому мне нужен запрос для извлечения всех записей в порядке

1 ответ

with cte as (
 select C.CompanyID, 0 as Level
 from COMPANY as C
 where C.CompanyID not in (select T.ChildCompanyID from COMPANYMAPPING as T)

 union all

 select C.ChildCompanyID as CompanyID, A.Level + 1 as Level
 from cte as A
 inner join COMPANYMAPPING as C on C.ParentCompanyID = A.CompanyID
)
select
 C.CompanyID, C.CompanyName, A.Level
from cte as A
 inner join COMPANY as C on C.CompanyID = A.CompanyID

sql fiddle demo

licensed under cc by-sa 3.0 with attribution.