My table in the database
How can I get my result as
Where child = 'chl1'?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.