October 9, 2007 at 11:41 am
In our hierarchy, we have 12 classes which represent all possbile objects in
the hierarchy. All Products (1), Category (3), Subcategory (4), Product
(5), Supplier (6), DLine (7), Division (9), Group (10), Department (11),
Director (12), Sr. Bus. Mgr. (13), Bus. Mgr. (14). Class IDs in parenthesis
are defined in ProdcutClasses table. Product
All objects for all classes are defined in the ProductObjects table. The PK
is ProdNodeID.
All defined parent-child relationships between classes and all objects
contained withing those classes are defined in the the ProductRelationships
table.
There are four defined paths between classes in the hierarchy, which are stored in the ProductStructure table. They are:
1, 12, 13, 14, 5
1, 7, 10, 11, 3, 4, 5
1, 9, 10, 11, 3, 4, 5
1, 6, 5
I need to show data lineage throughout the hierarchy. A typical recursive routine works fine until a chile node in the hierarchy that has multiple parents is encountered, which could occur at multiple levels in the hierarchy. The assignment of the parent node for the child node fails. For example, if I start out with class 5, there are three classes as its parents. Moving further up the hierarchy, class 10 has classes 7 and 9 as its parents.
DECLARE @ProductNodeID AS INTEGER
SET @ProductNodeID = 359
WHILE EXISTS
(SELECT *
FROM dbo.productrelationships as pr
where pr.ChildProdNodeId = @ProductNodeID)
BEGIN
EXEC dbo.sp_TraverseProductHierarchy @ProductNodeID;
-- Displays informtion about node
SET @ProductNodeID =
(SELECT pr.ParentProdNodeID
FROM dbo.ProductRelationships as pr
WHERE pr.ChildprodNodeID = @ProductNodeID) <-- Problem with multiple parents.
END;
From the reading I have been doing, it seems that all the examples related
to trees and hierarchies assume 0 or 1 parent.
Is there an efficient solution to traversing a hierarchy such as the one I
am working with when multiple parents exist?
October 9, 2007 at 11:51 pm
Use a CTE.
They are described in Books Online. There are also examples of how to traverse hierarchies with recursive CTE's.
N 56°04'39.16"
E 12°55'05.25"
October 10, 2007 at 7:03 am
Thanks. I did look into CTEs; however, because a child node may have multiple parents at different levels of the hierarchy and the fact that I did not have an anchor node, they did not seem like the best solution. I was able to create a recursive procedure, which is a variation of Joe Celko's UpTreeTraversal procedure that checks for multiple parents before traversing the hierarchy and then calling itself. Additional indepth testing is required, but it looks as though I on the right track. I hope.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply