title: Single hierachical query for both all ancestors/parents and children (DB2/SQLServer)
body: <p><br>
  I have found the solution for Oracle using UNION ALL on two hierarchical CONNECT BY queries, one fetching the ancestors and another the children.<br>
  I want to achieve the same for both <strong>DB2</strong> and <strong>SQL Server</strong>.<br>
  I know one element it could be a root, branch or leaf on the hierarchy. I need to fetch its whole hierarchy.</p>
  
  <p>Suppose I have <em>itemid='item3' and class='my class'</em>, I need to find its ancestors and children, I came up with:</p>
  
  <pre><code>with ancestor (class, itemid, parent, base, depth)
  as (
      select root.class, root.itemid, root.parent, root.itemid, 0
      from item root
      where root.class = 'myclass'
      and root.itemid = 'item3'
  --      union all
  --  select child.class, child.itemid, child.parent, root.base, root.depth+1
  --  from ancestor root, item child
  --  where child.class = root.class
  --  and child.parent = root.itemid
          union all
      select parent.class, parent.itemid, parent.parent, parent.itemid, root.depth-1
      from ancestor root, item parent
      where parent.class = root.class
      and parent.itemid = root.parent
  )
  select distinct class, itemid, parent, base, depth
  from ancestor 
  order by class, base, depth asc, itemid
  </code></pre>
  
  <p>I want result like this:</p>
  
  <pre><code>class      itemid     parent     base     depth
  myclass     item1     null      item3        -2
  myclass     item2     item1     item3        -1
  myclass     item3     item2     item3        0
  myclass     item4     item3     item3        1
  myclass     item5     item5     item3        2
  </code></pre>
  
  <p>If the above SQL is run I get the ancestors fine. Now if I remove the comments it seems to be on a infinite loop. There must be a way to make that work.<br>
  I am able to get the results in hierarchy one direction (ancestor or children) fine, but I am unable to get both on a single query.<br>
  Did anyone ever tried something like that?  </p>
  
  <p>Thanks</p>