September 13, 2011 at 2:20 am
Hi all,
create table #temp
(
ID int
,IntroducerID int
)
insert into #temp
select 1,1 union all
select 2,1 union all
select 3,2 union all
select 4,2 union all
select 5,3 union all
select 6,3 union all
select 7,4 union all
select 8,4 union all
select 9,5 union all
select 10,5
SELECT * FROM #TEMP
Query need To select internal dependencies (like tree structure)
Expected Output:
IF IntroducerID=3 then Output should be
IDIntroducerID
53
63
95
105
IF IntroducerID=2 then Output should be
IDIntroducerID
32
42
53
63
74
84
95
105
IF IntroducerID=1 then Output should be
IDIntroducerID
11
21
32
42
53
63
74
84
95
105
Please help me
--Ranjit
September 13, 2011 at 7:54 am
Nice job setting up the ddl and sample data. You have to use a recursive cte for this.
declare @IntroducerID int = 2
;with cte as (
select ID, IntroducerID from #temp where IntroducerID = @IntroducerID
union all
select t.ID, t.IntroducerID from #temp t
inner join cte on cte.ID = t.IntroducerID
)
select * from cte order by ID
Be careful with that as your sample data has a record that references itself. Your first record will cause this to crash because it is an infinite recursion.
If possible, you might consider looking into nested sets instead of the adjacency model you have here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply