Select hierarchy from table

Question

I have a table ServiceItem that has Id, ParentId and some properties like Name, Description and so on. Max level of hierarchy is 2. I need a query that selects rows with some condition, for example Name = '123' and its parent row to get smth like:

Id  ParentId  Name
1   NULL      '12' 
2   1         '123'

I have tried this:

SELECT
    *
FROM ServiceItem si
WHERE si.Name = '123'
    OR EXISTS (
        SELECT
            *
        FROM ServiceItem syst
            JOIN ServiceItem si2
                ON si2.ParentId = syst.Id
        WHERE syst.Id = si.ParentId
            AND si2.Name = '123'

    )

But it returns parent and all of it's children. Is there any chance to do it with one query? I'm using T-SQL to do it.

It's differs from this question because i need to get a bunch of rows, not only Id by path and conditions in my query could be different.

Answer 1

You can use a Common Table Expression with recursion:

WITH cte AS
  (
    SELECT     *
    FROM       ServiceItem
    WHERE      Name = '123'
    UNION ALL
    SELECT     *
    FROM       ServiceItem si
    INNER JOIN cte
            ON cte.ParentId = si.Id
  )
SELECT * FROM cte

For a more in-depth example, see this Q&A

Answer 2

WITH cte AS
  (
    SELECT     *
    FROM       ServiceItem
    WHERE      Name = '123'
    UNION ALL
    SELECT     *
    FROM       ServiceItem si
    INNER JOIN cte
            ON cte.ParentId = si.Id
  )
SELECT * FROM cte

It's a good query, but I also found this one:

SELECT
    *
FROM ServiceItem si
WHERE si.Name  = '123'
    OR EXISTS (
        SELECT
            *
        FROM ServiceItem si2
        WHERE si2.Name = '123'
            and si2.ParentId = si.Id
    )