how to find employee manager in sql without using self join

Question

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

Priyanka Jain · Answer

Your Query can be like below create table #Manager ( ManagerId INT , ManagerName VARCHAR (50) ) create table #Emp ( EmpId INT , EmpName VARCHAR (30), ManagerId INT ) INSERT INTO #Manager VALUES (1, 'ABC' ) INSERT INTO #Manager VALUES (2, 'XYZ' ) INSERT INTO #Emp VALUES (1, 'E01' ,1) INSERT INTO #Emp VALUES (2, 'E02' ,1) INSERT INTO #Emp VALUES (3, 'E03' ,2) INSERT INTO #Emp VALUES (4, 'E04' ,2) SELECT DISTINCT EmpId,EmpName,m.ManagerId,ManagerName FROM #Emp e,#Manager m WHERE e.ManagerId = m.ManagerId GROUP BY EmpId,EmpName,m.ManagerId,ManagerName The result will be displayed as below

Priyanka Jain · Answer

create table #Emp ( EmpId INT , EmpName VARCHAR (30), ManagerId INT ) INSERT INTO #Emp VALUES (1, 'E01' ,1) INSERT INTO #Emp VALUES (2, 'E02' ,1) INSERT INTO #Emp VALUES (3, 'E03' ,2) INSERT INTO #Emp VALUES (4, 'E04' ,2) INSERT INTO #Emp VALUES (5, 'E04' ,1) SELECT DISTINCT e.EmpId,e.EmpName,m.EmpId,m.EmpName FROM #Emp e,#Emp m WHERE e.ManagerId = m.empId GROUP BY e.EmpId,e.EmpName,m.EmpId,m.EmpName The output will be as below

Aakash Maurya · Answer

In that case your query should be like Inner Query. Select [Name] FROM Emp WHERE ManagerID in (Select EmpID From Emp) Just a sample which we can use

Jaimin Shethiya · Answer

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

Tushar Dikshit · Answer

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

Jignesh Kumar · Answer

Hi Sonu, You can use left join. http://venkatsqlinterview.blogspot.com/2011/05/self-join-with-example.html

Harsh Shah · Answer

You Can try this query if you don't want to use self join as shown in below image Output:

Amit Kumar · Answer

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/

Sonu Gupta · Answer

Hi Priyanka, manager also employee in tblEmployee For Example - MS is a manager of AK EMPLID EmpName ManagerID 7 Ak 19 2 AT 7 19 MS 3

Priyanka Jain · Answer

Hi Sonu If there are only 3 fields and only one table than from where Manager Name is coming from ?. Please let me know

Sonu Gupta · Answer

Hi Priyanka, There are only one table tblEmployee and its contain three fields - EmpID,EmpName,ManagerID. Note - Find employee manager without using self join Result like this EMPLID EmpName ManagerID ManagerName 7 Ak 19 MS

Sonu Gupta · Answer

Table Stracture - EMPLID EmpName ManagerID 7 Ak 19 2 AT 7 19 MS 3 Result EMPLID EmpName ManagerID ManagerName 7 Ak 19 MS

Guest User · Answer

Perhaps a sub-select. Any reason why you can't/won't use a self-join?

Amit Gupta · Answer

Use sub query for that