CODEWITHSUNDEEP
sql-servert-sql
user5094402
user5094402

Reputation:

Get full hierarchy by children

I have 2 tables:

  1. contains full tree data

enter image description here

  1. contains only specific childs

I need to get full hierarchy by child values. I can do it by one specific child node by following way: 

;with tree as 
(
select id, parent_id, name, level from f_all  where id = @specefic_id
union all 
select f.id, f.parent_id, f.name, f.level from f_all  f 
inner join  tree  t on f.id = t.parent_id and f.id <> f.parent_id 
)
select *
from tree  
OPTION (Maxrecursion 0)

I have an idea but I think it is not good. My idea is create function with above code. And call it by select my second table. I even didn't try it. Can you give me a right direction.

Upvotes: 0

Views: 107

Answers (2)

user5094402
user5094402

Reputation:

The problem for me was that i didn't know how cte recursion works. Now i know how it works line by line: Recursive Queries Using Common Table Expressions. Code below returns all hierarchy by children nodes:

;with tree as(
select id, parent_id, name, level from f_all fa
inner join @2nd_table_cildren_id c on c.id = fa.id
union all 
select f.id, f.parent_id, f.name, f.level from f_all  f 
inner join  tree  t on f.id = t.parent_id and f.id <> f.parent_id 
)
select distinct *
from tree  
OPTION (Maxrecursion 0)

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

This is 2012+ ( Using concat() ... easily converted ). 

Declare @f_all table (id int,parent_id int,name varchar(50))
Insert into @f_all values 
(1,null,'1'),(2,1,'2'),(3,1,'3'),(4,2,'4'),(5,2,'5'),(6,3,'6'),(7,null,'7'),(8,7,'8')

Declare @Top    int         = null      --<<  Sets top of Hier Try 9
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability
Declare @Filter varchar(25) = '4,6'     --<<  Empty for All or try 4,6

;with cteP as (
      Select Seq  = cast(1000+Row_Number() over (Order by name) as varchar(500))
            ,ID
            ,parent_id
            ,Lvl=1
            ,name 
      From   @f_all 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parent_id,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',1000+Row_Number() over (Order by r.name)) as varchar(500))
            ,r.ID
            ,r.parent_id
            ,p.Lvl+1
            ,r.name 
      From   @f_all r
      Join   cteP p on r.parent_id = p.ID)
     ,cteR1 as (Select *,R1=Row_Number() over (Order By Seq) From cteP)
     ,cteR2 as (Select A.Seq,A.ID,R2=Max(B.R1) From cteR1 A Join cteR1 B on (B.Seq like A.Seq+'%') Group By A.Seq,A.ID )
Select Distinct 
       A.R1  
      ,B.R2
      ,A.ID
      ,A.parent_id
      ,A.Lvl
      ,name = Replicate(@Nest,A.Lvl-1) + A.name
 From cteR1 A
 Join cteR2 B on A.ID=B.ID
 Join (Select R1 From cteR1 where IIF(@Filter='',1,0)+CharIndex(concat(',',ID,','),concat(',',@Filter+','))>0) F on F.R1 between A.R1 and B.R2
 Order By A.R1

Returns (@Top=null and @Filter='4,6')

enter image description here

Return Full Hier (@Top=null and @Filter='')

enter image description here

Returns Just a portion (@Top=2 and @Filter='')

enter image description here

Upvotes: 1

Related Questions