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