How do I get the dependency tree from a table.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

Hi
Please help me in the below requirement.

I have a table in which two columns will be there

ChildColumn -> ParentColumn
---------------------------
100 -> 101
100 -> 102
101 -> 103
102 -> 104
102 -> 105
103 -> 106
107 -> 108

I want to take the ParentColumn value of 100. Also the inter-dependencies of the parent column values too.Meaning for 100 the direct parent value is 101 and 102. Since 101 and 102 are depending on 103, 104 and 105 finally 103 depends on 106. Hence I wanted the output as below.

Output
------
101
102
103
104
105
106
Basically I wanted an output of dependency tree. Please help me in this.

Thanks & Regards,
Mathi

Posted 23-Mar-14 22:13pm

Mathi2code

Updated 23-Mar-14 22:15pm

v2

Add a Solution

2 solutions

Add a Solution

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

Tomas Takac · Accepted Answer · 2014-03-24T11:57:00

Solution 1

This is exactly what recursive CTE is for:

SQL

with cte as
(
  select * from mytable where child = 100
  
  union all
  
  select t.* from mytable t
  inner join cte on cte.parent = t.child
)
select parent from cte

Posted 24-Mar-14 11:57am

Tomas Takac

Comments

Maciej Los 24-Mar-14 19:21pm

Exactly!
+5!

Mathi2code 25-Mar-14 1:31am

Thanks +5.

Maciej Los · Accepted Answer · 2014-03-24T13:27:00

Solution 2

Solution 1 by Tomas Takac is very good. I'd like to add some extra links:
WITH common_table_expression (Transact-SQL)[^]
Recursive Queries Using Common Table Expressions[^]
Common Table Expressions[^]
CTE In SQL Server[^]

Posted 24-Mar-14 13:27pm

Maciej Los

Comments

Mathi2code 25-Mar-14 1:21am

Thanks Maciej

Maciej Los 25-Mar-14 2:50am

You're welcome ;)

How do I get the dependency tree from a table.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How do I get the dependency tree from a table.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us