Xtykutl

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?

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.