title: Select hierarchy from table
body: <p>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 <code>Name = '123'</code> and its parent row to get smth like:</p>
  
  <pre class="lang-none prettyprint-override"><code>Id  ParentId  Name
  1   NULL      '12' 
  2   1         '123'
  </code></pre>
  
  <p>I have tried this:</p>
  
  <pre><code>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'
  
      )
  </code></pre>
  
  <p>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.</p>
  
  <p>It's differs from <a href="https://stackoverflow.com/questions/37197500/how-to-traverse-a-path-in-a-table-with-id-parentid">this question</a> because i need to get a bunch of rows, not only Id by path and conditions in my query could be different.</p>