how to find employee manager in sql without using self join

Sonu Gupta

7y4.9kSQL

how to find employee manager in sql without using self join (There are table tblEmployee its contain three fields - EmpID,EmpName,ManagerID)

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaimin Shethiya7y ago
Hello Sonu,

can you please try the below script for
CREATE TABLE #tmpEmployee
(
   EmpId INT,
   EmpName VARCHAR(30),
   ManagerId INT
)
INSERT INTO
   #tmpEmployee
VALUES
   (1,'Emp-01', 1),
   (2,'Emp-02', 1),
   (3,'Emp-03', 2),
   (4,'Emp-04', 2)
SELECT
   DISTINCT
      e.EmpId,
      e.EmpName,
      m.EmpId ManagerId,
      m.EmpName ManagerName
FROM
   #tmpEmployee e,
   #tmpEmployee m
WHERE
   e.ManagerId = m.empId
GROUP BY
   e.EmpId,
   e.EmpName,
   m.EmpId,
   m.EmpName
DROP TABLE #tmpEmployee
let me know if you have any other help needed.

Thanks
0
Tushar Dikshit7y ago
HI,

You can use CTE.
;with empCTE as
(
select e.empid, e.empname, e.managerid,
CAST('' as varchar(50)) as Mname from employee e
where managerid = 0
union all
select e1.empid, e1.empname, e1.managerid,
CAST(c.empname as varchar(50)) as Mname from employee e1
inner join empCTE as C on e1.managerid=c.empid
where e1.managerid>0
) select * from empCTE
My post for such queries (Most asked SQL queries)-

https://www.c-sharpcorner.com/article/most-asked-sql-queries-in-interview-questions/

Thanks,
Tushar
0
Harsh Shah7y ago
You Can try this query if you don't want to use self join as shown in below image

Output:

0
Jignesh Kumar7y ago
Hi Sonu,

You can use left join.
http://venkatsqlinterview.blogspot.com/2011/05/self-join-with-example.html
+1
Amit Kumar7y ago
Hi, you can use recursive CTE but still you have to use a join Look at this example: WITH MyCTE AS ( SELECT EmpID, FirstName, LastName, ManagerID FROM Employee WHERE ManagerID IS NULL UNION ALL SELECT EmpID, FirstName, LastName, ManagerID FROM Employee INNER JOIN MyCTE ON Employee.ManagerID = MyCTE.EmpID WHERE Employee.ManagerID IS NOT NULL ) SELECT * FROM MyCTE please refer to this link for detail of CTE. http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/
0
Priyanka Jain7y ago
1. create table #Emp
2. (
3. EmpId INT,
4. EmpName VARCHAR(30),
5. ManagerId INT
6. )
9. INSERT INTO #Emp VALUES(1,'E01',1)
10. INSERT INTO #Emp VALUES(2,'E02',1)
11. INSERT INTO #Emp VALUES(3,'E03',2)
12. INSERT INTO #Emp VALUES(4,'E04',2)
13. INSERT INTO #Emp VALUES(5,'E04',1)
15. SELECT DISTINCT e.EmpId,e.EmpName,m.EmpId,m.EmpName FROM #Emp e,#Emp m
16. WHERE e.ManagerId = m.empId
17. GROUP BY e.EmpId,e.EmpName,m.EmpId,m.EmpName
The output will be as below
+1