I have two tables toplevel and parentchild. parentchild table is a tree which is related to each other. Tree looks like this.
TREE
1
-11
2
-12
--13
3
-14
--15
---16
drop table #TopLevel
create table #TopLevel
(
TopLevelID INT,
createdate DateTime
)
insert into #TopLevel
(TopLevelID,createdate
)
select 1,'2013-03-01 00:00:00' union all
select 2,'2013-03-07 00:00:00' union all
select 3,'2013-03-06 00:00:00' union all
select 4,'2013-03-03 00:00:00' union all
select 5,'2013-03-08 00:00:00' union all
select 6,'2013-03-09 00:00:00' union all
select 7,'2013-03-10 00:00:00'
drop table #parentchild
create table #parentchild
(
parentchildID INT,Parent INT,Child INT
)
insert into #parentchild
(
parentchildID,Parent, Child
)
select 1,1,11 union all
select 2,12,13 union all
select 4,15,16 union all
select 5,14,15 union all
select 3,2,12 union all
select 6,3,14
;with abc as
(
select * From #parentchild
left outer join #TopLevel on #parentchild.Parent=#TopLevel.TopLevelID
)
select * from abc
I need to find toplevelid for each row in #parentchild table. For examble in #parentchild table parent=12 is not in #toplevel table because its child too. then if we see child =12 and parent is 2, that is in #toplevel table.
please help. Thanks.
Data should be look like this in #parentchild table. * one i added manually.
parentchildID Parent Child TopLevelID createdate
1 1 11 1 2013-03-01 00:00:00.000
2 12 13 *2 *2013-03-07 00:00:00.000
4 15 16 *3 *2013-03-06 00:00:00.000
5 14 15 *3 *2013-03-06 00:00:00.000
3 2 12 2 2013-03-07 00:00:00.000
6 3 14 3 2013-03-06 00:00:00.000
Where I am doing wrong?
;with abc as
(
select ParentChildID,Parent,Child,TopLevelID,CreateDate From #parentchild
left outer join #TopLevel on #parentchild.Parent=#TopLevel.TopLevelID
)
,xyz as
(
select ParentChildID,Parent,Child,TopLevelID,CreateDate from abc where TopLevelID IS NULL
union all
select a.ParentChildID,a.Parent,a.Child,a.TopLevelID,a.CreateDate from abc a
inner join abc e on e.TopLevelID=a.Parent
)
select * from xyz