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
123 and want to find the
ID of the parent object with
How can I solve this with SQL?
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
sitein 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 = siteat the very end.