March 11, 2015 at 5:02 am
I've got a fairly large hierarchy table and I'm trying to put together a query to find the lowest level descendants of the hierarchy. I think there must be some way to use the "Breadth-first" approach that's stated in the MSDN technet sites about SQL Server HierarchyID but i'm not sure how to write the necessary T-SQL to traverse that. I know I can get all the descendants of a parent node like this
SELECT *
FROM AdventureWorks2012.HumanResources.Employee
WHERE OrganizationNode.IsDescendantOf(@ParentNode) = 1
However, this query returns all levels for that parent's branch. If I just wanted list of employees that were at the lowest level of the branch(es) for this parent node, how would I do this?
March 11, 2015 at 8:04 am
I might be hallucinating, but I think this may be the correct answer?
SELECT hrc.*
FROM HumanResources.Employee hrc -- children
left join HumanResources.Employee hrdc -- descendants of children
on hrdc.OrganizationNode = hrc.OrganizationNode.GetDescendant(null, null)
WHERE hrc.OrganizationNode.IsDescendantOf(@ParentNode) = 1
AND hrdc.OrganizationNode IS NULL
I guess I'll have to test how fast this is compared to a CTE unless someone has a blog post or article to point me to. Is it a good assumption that using the HIERARCHYID to traverse hierarchies in SQL Server are inherently faster than using a CTE?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply