Thanks for your help. Its working 🙂
I've tried with left join to get the detail and got the results. Is this correct?
CREATE TABLE [#Employee](
[EmpID] [int] NOT NULL,
[EmpName] [varchar](50) NOT NULL,
[SupID] [int] NULL)
GO
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (1, 'ram', 2)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (2, 'sureh', 3)
INSERT INTO [#Employee] (EmpID, EmpName, SupID) VALUES (3, 'vimal', null)
declare @Emp varchar(10)
select @Emp = 'ram'
;WITH CTE_emp_hierarchy AS
(
select
e1.empname employee,
m1.empname Manager1,
m2.empname Manager2
from
#employee e1
left join #employee m1 on (e1.supid = m1.empid )
left join #employee m2 on (m1.supid = m2.empid )
where
e1.empname = @Emp
)
select * from CTE_emp_hierarchy