CODEWITHSUNDEEP
sql-serverparent-childcommon-table-expression
SpeedOfRound
SpeedOfRound

Reputation: 1278

Finding all rows in hierarchy from any position in the tree

I have a table with a one to many relationship to keep track of what tickets are related to each other. A ticket can have any number of children and parents. 

LNK_TIC_Parent    LNK_TIC_Child
--------------    ----------  
       1              2  
       1              3          
       4              3              
       4              5                        
       5              6


       1          4
   ___/ \___   __/ \__
  /         \ /       \
 2           3         5
                        \
                         6

I'm trying to select a tickets whole ancestry regardless of where in the tree you start from. So in this example, If you start from ticket 6, or ticket 3, or ticket 1, you will get all 5 rows returned. Order doesn't matter to me.

I'm new to CTE's, so bear with me, this is what I have right now:

WITH linktree AS (
    SELECT
       LNK_TIC_Parent,LNK_TIC_Child
    FROM TicketLinks
  WHERE LNK_TIC_Parent = 1 OR LNK_TIC_Child = 1

UNION ALL

SELECT C.LNK_TIC_Parent,C.LNK_TIC_Child
    FROM TicketLinks C
INNER JOIN linktree C2 ON C.LNK_TIC_Parent = C2.LNK_TIC_Child
)

SELECT * FROM linktree

This will return the direct descendants of ticket 1 only. I tried adding another CTE and flipped the inner join to

ON C2.LNK_TIC_Child = C.LNK_TIC_Parent

But it didnt affect anything, I wasn't really sure where I should have put it anyways. I'm not even sure if this is in the scope of what CTEs can do.

Upvotes: 0

Views: 326

Answers (1)

Ben Thul
Ben Thul

Reputation: 32687

Here's what I've got:

DECLARE @start INT = 3;
WITH [data] AS (
    SELECT * FROM (VALUES
       (1, 2),  
       (1, 3),          
       (4, 3),              
       (4, 5),                        
       (5, 6)
    ) AS x(parent, id)
), [going_up] AS (
    SELECT [data].[parent], [data].[id]
    FROM [data]
    WHERE [data].[id] = @start

    UNION ALL

    SELECT child.[parent], child.[id]
    FROM [going_up] AS [parent]
    JOIN [data] AS [child]
        ON child.[parent] = [parent].[id]
), [going_down] AS (
    SELECT [going_up].[parent] AS [id],
           NULL AS [parent]
    FROM [going_up]

    UNION ALL

    SELECT child.id, child.parent
    FROM data AS child
    JOIN [going_down] AS [parent]
        ON child.[parent] = [parent].[id]
)
SELECT * 
FROM [going_down]

Essentially, I broke it up into two different recursive queries; one that goes to all the way up the tree given a starting position and then, once those nodes are determined, goes all the way back down. You'll notice that there are duplicate IDs in the output. This is because there are multiple ways to get to some nodes (specifically, 3 in your sample data). The output shows each path. If that's not important to you, just do select distinct(ID)… and call it good.

Upvotes: 1

Related Questions