March 29, 2010 at 10:09 am
I'm trying to figure out how to display the original parent id from records that have an id linking back (IdXref) to the previous. This can be an unknown number of levels deep. As shown in the desired results below, id 5 links back to 3, and 3 then links back to 1, so the master parent id for 5 would be 1, and so on.
create table #Data (Id int null, IdXref int null)
insert #Data (Id, IdXref)
select 1, null
union all
select 2, 1
union all
select 3, 1
union all
select 4, 1
union all
select 5, 3
union all
select 6, 3
union all
select 7, 6
union all
select 8, null
union all
select 9, null
union all
select 10, 9
/*
Desired results:
Id Xref MasterParent
1 1
2 1 1
3 1 1
4 1 1
5 3 1
6 3 1
7 6 1
8 8
9 9
10 9 9
11 10 9
*/
March 29, 2010 at 12:41 pm
How bout something like this?
;With prnt (id, mpid,lvl) AS
(Select d.id,isnull(d.idxref,d.Id),1
From #Data d
UNION ALL
Select d.id, p.mpid,p.lvl+1
From #Data d
Inner Join prnt p
on d.IdXref = p.id
)
Select p.id,p.mpid
from prnt p
Inner Join (select id, max(lvl) lvl From prnt group by id) mx
on mx.id = p.id and mx.lvl = p.lvl
order by 1
OPTION (MAXRECURSION 200);
April 16, 2010 at 11:10 am
The table that I am adding the MasterParent column to is very large. When records are added to the table, i understand I need to traverse back through the entire table until i find the MasterParent; however, I do not want to find the MasterParent with every single record, only those that are new that havent been assigned the MasterParent.
Any suggestions?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply