How to get the parent node in a table using the variable of the variable of type hierarchyid in sql by id(EmployeeID)? this is my table
CREATE TABLE Employee
(
Node hierarchyid PRIMARY KEY CLUSTERED,
EmployeeID int UNIQUE NOT NULL,
EmpName varchar(20) NOT NULL,
Title varchar(20) NULL
) ;
GO
This will get you the immediate manager of the id specified in @h
declare @h hierarchyid;
select *
from dbo.Employee
where Node = @h.GetAncestor(1);
While this will get you all of the managers up the chain:
select *
from dbo.Employee
where @h.IsDescendantOf(Node) = 1
With that last one, a node is considered a descendant of itself. If you don't want the query to return the employee specified in @h, add the predicate and Node <> @h to the where clause.
EDIT:
Re-reading your question, it seems like you may want to pass in an EmployeeID and get the manager. Here's that:
select m.*
from dbo.Employee as e
join dbo.Employee as m
on e.Node.GetAncestor(1) = m.Node
where e.EmployeeID = <yourIDHere>
I find a simple way to solve my problem:
SELECT EmployeeID
FROM Employee
WHERE [Node] IN (
SELECT [Node].GetAncestor(1).ToString()
FROM Employee
WHERE EmployeeID=4
)
thanks for your answer!!!
Here is what will help you in understanding hierarchy and getting the parent_id (in the below example, I have called it ManagerID).
/*
Here is the problem definition:
1. Employees table contains the following columns a) EmployeeId, b) EmployeeName c) ManagerId
2. If an EmployeeId is passed, the query should list down the entire organization hierarchy i.e who is the manager of the EmployeeId passed and who is managers manager and so on till full hierarchy is listed.
Here are the two scenarios -
Scenario 1: If we pass Javed's EmployeeId to the query, then it should display the organization hierarchy starting from Javed.
Scenario 2: If we pass Nussenbaum's EmployeeId to the query, then it should display the organization hierarchy starting from Nussenbaum.
*/
--Here is the test data
IF OBJECT_ID ('tempdb..#Employees') IS NOT NULL
DROP TABLE #Employees
Create table #Employees
(
EmployeeID int primary key identity,
EmployeeName nvarchar(50),
ManagerID int foreign key references #Employees(EmployeeID)
)
GO
Insert into #Employees values ('Sonal', NULL)
Insert into #Employees values ('Angus', NULL)
Insert into #Employees values ('Nik', NULL)
Insert into #Employees values ('Abu', NULL)
Insert into #Employees values ('Nussenbaum', NULL)
Insert into #Employees values ('Anirudh', NULL)
Insert into #Employees values ('Javed', NULL)
Insert into #Employees values ('Ron', NULL)
Insert into #Employees values ('Matt', NULL)
Insert into #Employees values ('Nikhil', NULL)
GO
Update #Employees Set ManagerID = 8 Where EmployeeName IN ('Angus', 'Nik', 'Nussenbaum')
Update #Employees Set ManagerID = 2 Where EmployeeName IN ('Matt', 'Anirudh')
Update #Employees Set ManagerID = 3 Where EmployeeName IN ('Abu')
Update #Employees Set ManagerID = 5 Where EmployeeName IN ('Sonal', 'Nikhil')
Update #Employees Set ManagerID = 4 Where EmployeeName IN ('Javed')
GO
--Here is the SQL that does the job
Declare @ID int ;
Set @ID = 7;
WITH EmployeeCTE AS
(
Select EmployeeId, EmployeeName, ManagerID
From #Employees
Where EmployeeId = @ID
UNION ALL
Select #Employees.EmployeeId , #Employees.EmployeeName, #Employees.ManagerID
From #Employees
JOIN EmployeeCTE
ON #Employees.EmployeeId = EmployeeCTE.ManagerID
)
Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId
