Neel Sarvaiya Neel Sarvaiya - 1 month ago 6
SQL Question

Query to find all the parent and child nodes of a given node in SQL

I have seen many examples of the getting all the child nodes (including current node) in SQL using the CTE. Simple example is below:

;WITH #results AS
(
SELECT ChildId,
ParentId
FROM History
WHERE ChildId= @selected
UNION ALL
SELECT t.ChildId,
t.ParentId
FROM History t
INNER JOIN #results r ON r.ExpirationList = t.ParentId
)
SELECT *
FROM #results;


The above query gives me all the child nodes for a given node. For eg:
A -> B -> C -> D -> E and I pass @selected = "C", then I get results as C -> D -> E.

My question is how do I get the complete chain, irrespective of what I pass.
If @selected = "D", then I want results as A -> B -> C -> D -> E and
if @selected = "A", then I want results as A -> B -> C -> D -> E.

I want both parent and child nodes for the given nodes. Can someone help me with the query for the same?

Answer

This should work:

;WITH #results1 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results1 r ON r.ExpirationList = t.ParentId 
)
,#results2 AS
(
    SELECT  ChildId, 
            ParentId 
    FROM    History
    WHERE   ChildId= @selected
    UNION ALL
    SELECT  t.ChildId, 
            t.ParentId 
    FROM    History t
            INNER JOIN #results2 r ON t.ExpirationList = r.ParentId 
)
SELECT  *
FROM    #results1
UNION
SELECT  *
FROM    #results2
Comments