Consulta jerárquica única para todos los antepasados ​​/ padres e hijos(DB2/SQLServer)

sql-server hierarchical

Question

Encontré la solución para Oracle usando UNION ALL en dos consultas jerárquicas CONNECT BY, una buscando a los antepasados ​​y otra a los niños.
Quiero lograr lo mismo para DB2 y SQL Server .
Sé que un elemento podría ser una raíz, una rama o una hoja en la jerarquía. Necesito buscar toda su jerarquía.

Supongamos que tengo itemid = ''item3'' y class = ''my class'' , necesito encontrar sus ancestros e hijos, se me ocurrió:

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

Quiero un resultado como este:

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

Si se ejecuta el SQL anterior obtengo los ancestros bien. Ahora si elimino los comentarios, parece estar en un bucle infinito. Debe haber una manera de hacer que eso funcione.
Puedo obtener los resultados en jerarquía de una dirección (antecesor o hijos) bien, pero no puedo obtener ambos en una sola consulta.
¿Alguna vez alguien intentó algo así?

Gracias

Answer 1

En la consulta comentada, debes mencionar que parent.parent es NULL

Por favor revisa este http://msdn.microsoft.com/en-us/library/ms186243.aspx

USE AdventureWorks2008R2; GO WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level) AS (
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID )
-- Statement that executes the CTE SELECT ManagerID, EmployeeID, Title, DeptID, Level FROM DirectReports INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID WHERE dp.GroupName = N''Sales and Marketing'' OR Level = 0; GO

Answer 2

Si no te importa hacerlo usando dos instrucciones WITH , el siguiente devuelve todo tu árbol de jerarquía.

Datos de prueba

DECLARE @item TABLE (
  class VARCHAR(32)
  , itemid VARCHAR(32)
  , parent VARCHAR(32)
)

INSERT INTO @item VALUES 
  (''myclass'', ''item1'', null)  
  , (''myclass'', ''item2'', ''item1'')  
  , (''myclass'', ''item3'', ''item2'')    
  , (''myclass'', ''item4'', ''item3'')    
  , (''myclass'', ''item5'', ''item4'')    

Declaración de SQL

;WITH children AS (
  SELECT  class
          , itemid
          , parent
          , base = itemid
          , depth = 0
  FROM    @item
  WHERE   class = ''myclass''
          AND itemid = ''item3''          
  UNION ALL
  SELECT  children.class
          , i.itemid
          , i.parent
          , children.base
          , children.depth + 1
  FROM    children
          INNER JOIN @item i ON i.parent = children.itemid
                                AND i.class = children.class
)
, parents AS (
  SELECT  *
  FROM    children
  WHERE   depth = 0
  UNION ALL
  SELECT  parents.class
          , i.itemid
          , i.parent
          , parents.base
          , parents.depth - 1
  FROM    parents
          INNER JOIN @item i ON i.itemid = parents.parent
                                AND i.class = parents.class                                  
)
SELECT  *
FROM    children
UNION 
SELECT  *
FROM    parents
ORDER BY depth

Answer 3

use 3 CTE

with 
ancestors as (...)
,children as (...)
,all_ as (select * from ancestors union all select * from children)
select * from all_