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.

Possible duplicate of [How to traverse a path in a table with id & parentId?](http://stackoverflow.com/questions/37197500/how-to-traverse-a-path-in-a-table-with-id-parentid) — , Dec 30 '16 at 08:03
Possible duplicate of [Sql server CTE and recursion example](http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example). You'll have to modify the query to start the tree with the bottom of the tree instead of the top, but it's essentially the same syntax. — Bacon Bits, Dec 30 '16 at 08:11
Then specify what the expected output is. The way you described it, you **are** looking for recursive CTE. — , Dec 30 '16 at 08:22

score 4 · Accepted Answer · edited May 23 '17 at 12:32

4

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

edited May 23 '17 at 12:32

Community

1
1

answered Dec 30 '16 at 08:17

trincot

317,000
35
244
286

score 0 · Answer 2 · answered Dec 30 '16 at 08:38

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
    )

Select hierarchy from table

2 Answers2