CODEWITHSUNDEEP
sqlsql-servert-sqlhierarchical-datarecursive-query
Dima Trygodko
Dima Trygodko

Reputation: 75

Select hierarchy from table

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.

Upvotes: 5

Views: 16392

Answers (2)

trincot
trincot

Reputation: 351278

You can use a Common Table Expression with recursion:

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

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

Upvotes: 6

Dima Trygodko
Dima Trygodko

Reputation: 75

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
    )

Upvotes: 0

Related Questions