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

Question

How do I get EmployeeID of the father passing the EmployeeID of the child in the table below using the type variable hierarchyid 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

Answer 1

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

In your case, it will return 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 the Sqlfiddle