recursive CTE with two tables in sql server

Question

0.00/5 (No votes)

See more:

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

SQL

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?

SQL

;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

Posted 15-May-14 12:00pm

Member 10821967

Updated 15-May-14 12:23pm

Nelek

v2

Add a Solution

Comments

Maciej Los 16-May-14 12:08pm

How does it possible, that:
PrentChildID = 2 is relatetd with TopLevelID = 2?
PrentChildID = 4 is relatetd with TopLevelID = 3?
PrentChildID = 5 is relatetd with TopLevelID = 3?
I haven't see relationship between them... ;(

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-05-16T06:09:00

Please, read my comment to the question first.

Here: SQL Wizardry Part Three - Common Table Expressions (CTEs)[^] you'll find sample code to get hierachical data.

But i think your structure is wrong. For further information, please see:
Please refer this: Hierarchical Data (SQL Server)[^]
Hierarchical and recursive queries in SQL[^]
Tutorial: Using the hierarchyid Data Type[^]
Hierarchies with HierarchyID in SQL 2008[^]
Hierarchies WITH Common Table Expressions[^]
Data Hierarchies With SQL Server 2008[^]
Hierarchical Queries with Common Table Expressions[^]

Jörgen Andersson · Answer 2 · 2014-05-16T09:39:00

This works:

SQL

with c(parentchildid,parent,child,toplevelid,rnk) as (
    select  
            parentchildid,
            parent,
            child,
            parent toplevelid,
            1 rnk
    from    parentchild pc
  union all
    select  
            pc.parentchildid,
            pc.parent, 
            pc.child, 
            c.toplevelid,
            c.rnk + 1 rnk
    from    parentchild pc
    join    c ON pc.parent = c.child
    )
,ranked as (    
    select  parentchildid,parent,child,max(rnk) as rnk
    from    c
    group by parentchildid,parent,child
    )
select  c.parentchildid,c.parent,c.child,c.toplevelid,tl.createdate
from    c
join    ranked r
    on  c.parentchildid = r.parentchildid
    and c.rnk = r.rnk
join    toplevel tl
    on  tl.toplevelid = c.toplevelid

But as you have already been told, it's not a good design.
Tell us what you want to achieve and we might give you a suggestion for a better design.

recursive CTE with two tables in sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0