Get the parent passing the child code using Hierarchical Data (SQL Server)

Question

Get the parent passing the child code using Hierarchical Data (SQL Server)

Navigation

#1 by (1 votes)

1

How do I get EmployeeID of the parent passing the EmployeeID of the child from the table below using the hierarchyid variable in SQL Server?

  CREATE TABLE Employee
    (
       Node hierarchyid PRIMARY KEY CLUSTERED,
       EmployeeID int UNIQUE NOT NULL,
       EmpName varchar(20) NOT NULL,
       Title varchar(20) NULL
    ) ;
    GO

sql sql-server

asked by anonymous 26.08.2015 / 22:21

1 answer

Criteria query with expression contains in the field of a ManyToOne entity Android Download Directory

score 1 · Accepted Answer

You can use the GetAncestor method, it returns the hierarchyid for the inserted position.

In your case, it will return to the first position. See the example below:

    SELECT 
         EmployeeID 
    FROM 
         Employee
    WHERE 
        [Node] IN (
                   SELECT
                       [Node].GetAncestor(1).ToString()
                   FROM
                       Employee
                   WHERE 
                       EmployeeID=4
                   )

You can also view through SQLFiddle