I want to make a tree of parents only in sql server

Question

My table in the database

How can I get my result as Where child = 'chl1'?

Answer 1

create table #t(child varchar(4), parent varchar(4))

insert into #t values ('ch1','ch')
insert into #t values ('ch','c1')
insert into #t values ('ch','c2')
insert into #t values ('bsd','chl1')
insert into #t values ('chl2','ch')
insert into #t values ('c1','c')

select * from #t


;WITH ch AS 
( 
--initialization 
SELECT child, parent
FROM #t 
WHERE child ='ch1'

UNION ALL 

--recursive execution 
SELECT #t.child, #t.parent
FROM #t 
JOIN ch on ch.parent = #t.child
) 
SELECT * FROM ch  

One of the solutions for hierarchical queries is a CTE recursively joined to itself. In the example before, we run initialization query:

SELECT child, parent FROM #t WHERE child ='ch1'

Then join it to itself (ch) using join condition ch.parent = #t.child until no records found or until the MAXRECURSION limit is reached. Default value is 100 but it could be overridden up to 32767 via an OPTION (MAXRECURSION xxx) hint. Zero can also be specified, meaning 'no limit'.

For background, see Recursive Queries using Common Table Expressions (CTE) in SQL Server by Edwin Sarmiento.