asked
recursive with two tables
I have two tables toplevel and parentchild. parentchild table is a tree which is related to each other. Tree looks like this. TREE 1 this is top level -11 2 this is top level -12 --13 3 this is top level -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
Use a recursive cte to build a hierarchy that maintains the id of the top level, at all levels. Then join that back to your source tables to fill in the other columns ;with cte as ( select TopLevelID as TreeItem, null as Parent, TopLevelID as TopLevel from #TopLevel union all select p.Child, p.Parent, c.TopLevel from #parentchild p join cte c on c.TreeItem = p.Parent ) select pc.parentchildID, pc.Parent, pc.Child, cte.TopLevel, tl.createdate from cte cte join #parentchild pc on pc.parent = cte.TreeItem join #TopLevel tl on tl.TopLevelID = cte.TopLevel gives you parentchildID Parent Child TopLevel createdate ------------- ----------- ----------- ----------- ----------------------- 1 1 11 1 2013-03-01 00:00:00.000 3 2 12 2 2013-03-07 00:00:00.000 2 12 13 2 2013-03-07 00:00:00.000 6 3 14 3 2013-03-06 00:00:00.000 5 14 15 3 2013-03-06 00:00:00.000 4 15 16 3 2013-03-06 00:00:00.000 (6 row(s) affected)
14 People are following this question.
