My table in the database

enter image description here

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

enter image description here

  • Can you post the tables as text rather than images? So anyone that answers it doesn't have to type this all out first Commented Aug 4, 2023 at 14:41

1 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 
SELECT child, parent
FROM #t 
WHERE child ='ch1'


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

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.

  • There are an error : The statement terminated. The maximum recursion 100 has been exhausted before statement completion Commented Aug 5, 2023 at 21:06
  • When I use option max recursion select statement result showing all child to all parents. I need to select all parents only Commented Aug 5, 2023 at 21:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.