CODEWITHSUNDEEP
sql-serverrecursioncommon-table-expression
mp252
mp252

Reputation: 475

How use recursive CTE content for specific child node?

I have a recursive CTE query in SQL Server which handles getting the depth of each parent and child values. However I want to get the full depth for only on specific child. For example the data I have looks something like this, I only want to get the results for where c_key is equal to child my initial query below the table gets the results for all links.

I tried to add a where clause in to the unioned select statement, but this just returned the the 0th depth rows.

c_key p_key
child teen
teen adult
adult old
young middle
middle old 
WITH cte_data (c_key, p_key, depth) AS
(
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE p_key = 'old'

    UNION ALL

    SELECT c.c_key, c.p_key, o.depth + 1 AS depth
    FROM cte_data o
    INNER JOIN my_table c ON o.c_key = c.p_key
)
SELECT * 
FROM cte_data

Upvotes: 0

Views: 325

Answers (1)

Anton Grig
Anton Grig

Reputation: 1719

Seemingly, you should swap columns for the join condition and change the where search condition according to your task condition.

WITH cte_data (c_key, p_key, depth) AS
(
    SELECT c_key, p_key, 0 AS depth
    FROM my_table
    WHERE c_key = 'child'

    UNION ALL

    SELECT c.c_key, c.p_key, o.depth + 1 AS depth
    FROM cte_data o
    INNER JOIN my_table c ON o.p_key = c.c_key
)
SELECT * 
FROM cte_data
ORDER by depth

Upvotes: 1

Related Questions