Querying a Hierarchical Table Using Hierarchy Methods

Sariya has one subordinate employee. To query for Sariya's subordinates, execute the following query that uses the IsDescendantOf method:

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46 ;

SELECT *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.IsDescendantOf(@CurrentEmployee) = 1 ;

The result lists both Sariya and Wanida. Sariya is listed because she is the descendant at the 0 level. Wanida is the descendant at the 1 level.

You can also query for this information by using the GetAncestor method. GetAncestor takes an argument for the level that you are trying to return. Since Wanida is one level underneath Sariya, use GetAncestor(1) as demonstrated in the following code:

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 46 ;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(1) = @CurrentEmployee

This time the result lists only Wanida.

Now change the @CurrentEmployee to David (EmployeeID 6) and the level to 2. Execute the following to also return Wanida:

DECLARE @CurrentEmployee hierarchyid

SELECT @CurrentEmployee = OrgNode
FROM HumanResources.EmployeeOrg
WHERE EmployeeID = 6 ;

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode.GetAncestor(2) = @CurrentEmployee

This time, you also receive Mary who also reports to David, two levels down.

As the hierarchy grows larger it is more difficult to determine where the members are in the hierarchy. Use the GetLevel method to find how many levels down each row is in the hierarchy. Execute the following code to view the levels of all the rows:

SELECT OrgNode.ToString() AS Text_OrgNode, 
OrgNode.GetLevel() AS EmpLevel, *
FROM HumanResources.EmployeeOrg ;
GO

Use the GetRoot method to find the root node in the hierarchy. The following code returns the single row which is the root:

SELECT OrgNode.ToString() AS Text_OrgNode, *
FROM HumanResources.EmployeeOrg
WHERE OrgNode = hierarchyid::GetRoot() ;
GO