0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Drupal developers and administrators

create table #location(locationid int, JuridicitionID int, placename varchar(30)) 
 
insert #location values(1, 0, 'USA') 
insert #location values(2, 1, 'Maryland')
insert #location values(4, 1, 'Kansas')
insert #location values(5, 1, 'Nebraska') 
insert #location values(6, 2, 'Silver Spring') 
insert #location values(7, 2, 'Golden Spring') 


 
declare @JuridicitionID int = 2

;with cte as 
( 
select placename, JuridicitionID, 1 lvl 
from #location where JuridicitionID = @JuridicitionID 
union all 
select t.placename, t.JuridicitionID, lvl + 1 
from cte join #location t on cte.JuridicitionID = t.locationid 
) 
select placename from cte 
order by JuridicitionID 

fork query

created oct 20 11 anonymous