Find a specific parent inside hierarchical data using T-SQL

user_9898813 0
Viewed 163

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 start object has).

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?

hierarchical-data tsql sql-server sql
1 Answers

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.

user_870150
Related