Have Geek Brain

SQL 2008 Hierarchy - Select X Descendants Down

How can I query a table with a column of data type HIERARCHYID and get a list of X levels of descendants under an employee?

Here's the current structure:

CREATE TABLE [dbo].[Employees](
    [NodeId] [hierarchyid] NOT NULL,
    [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [varchar](120) NULL,
    [MiddleInitial] [varchar](1) NULL,
    [LastName] [varchar](120) NULL,
    [DepartmentId] [int] NULL,
    [Title] [varchar](120) NULL,
    [PhoneNumber] [varchar](20) NULL,
    [IM] [varchar](120) NULL,
    [Photo] [varbinary](max) NULL,
    [Bio] [varchar](400) NULL,
    [Active] [bit] NULL,
    [ManagerId] [int] NULL
)
+2


a source to share


2 answers


I found my answer:



How to find ALL descendants using the HierarchyID for SQL Server

0


a source


I wanted to add a little to the above. Besides selecting a branch of the tree, you often want the descendants to have a certain depth. For this, many tables use an extra computed column for "depth" (something like [Depth] AS (myHierarchy.GetLevel]()

). With this extra column, you can run queries like the following to limit the depth.

SELECT @MaxDepth       = 3,

SELECT @theParent      = Hierarchy,
       @theParentDepth = Depth
FROM   myTreeTable T 
WHERE  T.RowID         = @RowID

SELECT    myHierarchy
FROM      myTreeTable T
WHERE     T.myHierarchy.IsDescendantOf(@theParent) = 1  AND
          T.Depth < (@theParentDepth  + @MaxDepth)


Note that you may want to index the computed column (perhaps in combination with some other columns or including some other columns) if you rely heavily on it.

+3


a source






More articles:


All Articles