Geek Answers Handbook

One hierarchical query for all ancestors / parents and children (DB2 / SQLServer)


I found a solution for Oracle using UNION ALL on two hierarchical CONNECT BY queries, one representing ancestors and the other representing children.
I want to achieve the same value for DB2 and SQL Server .
I know one element, it can be a root, branch or leaf in the hierarchy. I need to get my whole hierarchy.

Suppose I have itemid = 'item3' and class = 'my class', I need to find his ancestors and children, I came up with:

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

I want to get the result as follows:

 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

If the above SQL is running, I get the ancestors in order. Now, if I delete comments, this seems like an endless loop. There must be a way to make this work.
I can get the results in the hierarchy in one direction (ancestor or children) in order, but I can not get both from the same query.
Has anyone ever tried something like this?

thanks

+4
source share
3 answers

If you don't mind using two WITH statements, the following returns the entire hierarchy tree.

Test Data

 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')

SQL statement

 ;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
+1
source

In the requested request, you must specify parent.parent NULL

Please go through this 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
0
source

use 3 cte

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

More articles:


All Articles