How can I get linked list like (Parent - Child) mapped columns in a single Query?

Question

Consider The following Table

+--------+-------+--+
| Parent | Child |  |
+--------+-------+--+
|      1 |     2 |  |
|     10 |    13 |  |
|      2 |     3 |  |
|      3 |     4 |  |
|     13 |    14 |  |
|      4 |     5 |  |
|     15 |    16 |  |
|      5 |     1 |  |
+--------+-------+--+

In this table I'm following the hierarchy of parent child. From this table I want a result as the below table

+--------+-------+--+
| Parent | Child |  |
+--------+-------+--+
|      1 |     2 |  |
|      2 |     3 |  |
|      3 |     4 |  |
|      4 |     5 |  |
|      5 |     1 |  |
+--------+-------+--+

I want to get the hierarchy in my code (1-2-3-4-5-1). At present I'm querying for each child after getting its parent (Sometimes, Child can be any of previous Parents like 5-1). For a long hierarchy it will execute a number of queries. How can I make this more efficient?

possible duplicate of [SQL Query for Parent Child Relationship](http://stackoverflow.com/questions/207309/sql-query-for-parent-child-relationship) — Jodrell, Apr 24 '13 at 11:07
@Jodrell I want something different. I have another parent in the table which has its previous parent as its child again — Subin Jacob, Apr 25 '13 at 04:47
That should never happen in any good design. It can be avoided but is a different question altogether. So even in your front end code, you'd have 1-2-3-4-5-1-2-3-4-5-1-2-3-4-5-etc because there's no end, an infinite loop. — RichardTheKiwi, Apr 25 '13 at 04:58
@RichardTheKiwi But if I need to store such a loop in Table , What should be the design then? — Subin Jacob, Apr 25 '13 at 05:12
@RichardTheKiwi I have a Content Management system. Suppose there are article sections a1,a2,a3. When user make an input to a1, I want to make it available to a2 and a3. If its a3, then a2 and a1. For this, I mapped a1 -> a2-> a3-> a1. — Subin Jacob, Apr 25 '13 at 05:22

RichardTheKiwi · Answer 1 · 2013-04-25T05:32:49.560

4

;with cte(parent,child) as (
    select parent, child
      from sometable
     where parent = 1  --- seed
     UNION ALL
    select t.parent, t.child
      from sometable t
      join cte on cte.child = t.parent
)
    select *
      from cte;

To avoid infinite loops, you will have to store the list of traversed ids:

;with cte(parent,child,traversed) as (
    select parent, child, ',' + right(parent,10) + ','
      from sometable
     where parent = 1  --- seed
     UNION ALL
    select t.parent, t.child, cte.traversed + right(t.parent,10) + ','
      from sometable t
      join cte on cte.child = t.parent
     where not cte.traversed like ',%' + t.parent + '%,'
)
    select parent, child
      from cte;

But it won't run anywhere near as fast since it's having to do the LIKE checks.

edited Apr 25 '13 at 05:32

answered Apr 24 '13 at 10:47

RichardTheKiwi

105,798
26
196
262

I added this `(5 - 1 )` condition to my table. In my table I have this condition in real and this Query gives a different result here! – Subin Jacob Apr 25 '13 at 04:55
I have a Content Management system. Suppose there are article sections a1,a2,a3. When user make an input to a1, I want to make it available to a2 and a3. If its a3, then a2 and a1. For this, I mapped a1 -> a2-> a3-> a1. What should I do in the above context? (the order is important) – Subin Jacob Apr 25 '13 at 05:39

TechDo · Accepted Answer · 2013-04-25T07:25:53.693

2

Please try:

DECLARE @table as TABLE(Parent int, Child int)

insert into @table values
(1, 2),
(10, 13),
(2, 3),
(3, 4),
(13, 14),
(4, 5),
(5, 1)

select * from @table

declare @ParentID int
set @ParentID=1

;WITH T(Parent, Child)AS
    ( 
        SELECT Parent, Child from @table where Parent=@ParentID
        UNION ALL
        SELECT T1.Parent, T1.Child FROM @table T1 INNER JOIN T ON T1.Parent=T.Child
        WHERE T.Child<>@ParentID

    )
select * from T
order by Parent

edited Apr 25 '13 at 07:25

answered Apr 24 '13 at 10:49

TechDo

18,398
3
51
64

I had a value like this also (5, 1). When I execute Query for this Table it will show invalid result. How can I exclude this condition??? – Subin Jacob Apr 25 '13 at 04:41
Thank you for your apt solution – Subin Jacob Apr 25 '13 at 06:26
Can you tell me why this stored Procedure generates duplicates in the result set. http://pastie.org/7714494 – Subin Jacob Apr 25 '13 at 07:05
I didn't get you... which one is the duplicate? Does it solve by using distinct (`select distinct * from T`)? – TechDo Apr 25 '13 at 07:11
Thats solving the problem. But didn't get why its happening! – Subin Jacob Apr 25 '13 at 07:15
But the order seems to be different. How can I make it in an order? http://pastie.org/private/6oq5sduif5aahbdkfayihq – Subin Jacob Apr 25 '13 at 07:24

score -1 · Answer 3 · answered Apr 24 '13 at 10:50

-1

The manual covers that: http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx SO really shouldn't be used to for asking questions that already have good answers in the manuals.

answered Apr 24 '13 at 10:50

Jakub Kania

15,665
2
37
47

not true, its true that manuals shouldn't be used as answers but as references in answers. – Jodrell Apr 24 '13 at 10:51
http://meta.stackexchange.com/questions/168712/what-is-stack-overflows-take-on-link-only-answers – Jodrell Apr 24 '13 at 10:55
@Jodrell How about if I say it was already covered on SO: http://stackoverflow.com/questions/207309/sql-query-for-parent-child-relationship ? Looking for "sql server parent child" will bring up either of those two links therefore I believe the asker didn't put effort into his research. – Jakub Kania Apr 24 '13 at 11:05
That would be a valid comment and I'd be inclined to agree, see my vote. – Jodrell Apr 24 '13 at 11:08

How can I get linked list like (Parent - Child) mapped columns in a single Query?

3 Answers3