Find a specific parent inside hierarchical data using T-SQL

I've got objects with an hierarchical order. Each object has an ID, a parent object (TO_ID in the table) and a type. My data is in a table which looks like this:

ID  | TO_ID | TYPE
123 | 103   | group
176 | 103   | field
256 | 169   | group
103 | 234   | organization
234 | 390   | site

Now I want to search through the table until I find a parent object with a specific type (I don't know how many parent objects my have start object).

For example I start with ID 123 and want to find the ID of the parent object with TYPE site.

How can I solve this with SQL?

1 answer

  • answered 2018-11-08 08:05 Salman A

    You need a recursive CTE for this:

    DECLARE @t TABLE (ID INT, TO_ID INT, TYPE VARCHAR(100));
    
    INSERT INTO @t VALUES
    (123, 103, 'group'),
    (176, 103, 'field'),
    (256, 169, 'group'),
    (103, 234, 'organization'),
    (234, 390, 'site'),
    (390, 999, 'notme');
    
    DECLARE @start INT = 123;
    DECLARE @stop VARCHAR(100) = 'site';
    
    WITH cte AS (
        SELECT base.*, 1 AS LVL
        FROM @t base
        WHERE ID = @start
        UNION ALL
        SELECT curr.*, LVL + 1
        FROM @t curr
        INNER JOIN cte prev ON curr.ID = prev.TO_ID
        WHERE prev.TYPE <> @stop
    )
    SELECT *
    FROM cte
    ORDER BY LVL
    

    A recursive CTE is actually an iterative query. You start with some rows (the 123 row) and then you keep appending rows to the result of previous iteration until some criteria is met (you run out of rows or found site in the previous iteration). Here is the result:

    ID  | TO_ID | TYPE         | LVL
    123 | 103   | group        | 1
    103 | 234   | organization | 2
    234 | 390   | site         | 3
    

    If you're not interested in finding the complete path between the two nodes then remove the where clause from curr and add WHERE TYPE = site at the very end.