Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Self Join to get employee manager name

Hello I have a Employee Table with following columns

Emp_id, Emp_Name and Mgr_id.

I am trying to create a view which will list

Emp_id, Emp_name, Mgr_id and Mgr_name (by cross joining the Employee table). I tried outer join, inner join etc, but I am not able to get it right.

Any help is highly appreciated.

CREATE TABLE [dbo].[tblEmployeeDetails](
[emp_id] [bigint] NOT NULL, 
[emp_name] [nvarchar](200) NULL,    
[emp_mgr_id] [bigint] NULL, CONSTRAINT [PK_tblEmployeeDetails] PRIMARY KEY CLUSTERED (
[emp_id] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]
like image 697
Tippu Avatar asked Jul 11 '12 07:07

Tippu


4 Answers

SELECT e1.empno EmployeeId, e1.ename EmployeeName, 
       e1.mgr ManagerId, e2.ename AS ManagerName
FROM   emp e1, emp e2
       where e1.mgr = e2.empno
like image 109
niketan Avatar answered Nov 07 '22 11:11

niketan


CREATE VIEW AS
SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName, 
       e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName
FROM   tblEmployeeDetails e1
       JOIN tblEmployeeDetails e2
       ON e1.emp_mgr_id = e2.emp_id

EDIT: Left Join will work if emp_mgr_id is null.

CREATE VIEW AS 
SELECT e1.emp_Id EmployeeId, e1.emp_name EmployeeName,  
       e1.emp_mgr_id ManagerId, e2.emp_name AS ManagerName 
FROM   tblEmployeeDetails e1 
       LEFT JOIN tblEmployeeDetails e2 
       ON e1.emp_mgr_id = e2.emp_id
like image 35
Yaqub Ahmad Avatar answered Nov 07 '22 12:11

Yaqub Ahmad


   SELECT b.Emp_id, b.Emp_name,e.emp_id as managerID, e.emp_name as managerName
    FROM Employee b
    JOIN Employee e ON b.Emp_ID = e.emp_mgr_id

Try this, it's a JOIN on itself to get the manager :)

like image 8
PoeHaH Avatar answered Nov 07 '22 13:11

PoeHaH


CREATE VIEW EmployeeWithManager AS 
SELECT e.[emp id], e.[emp name], m.[emp id], m.[emp name] 
FROM Employee e LEFT JOIN Employee m ON e.[emp mgr id] = m.[emp id]

This definition uses a left outer join which means that even employees whose manager ID is NULL, or whose manager has been deleted (if your application allows that) will be listed, with their manager's attributes returned as NULL.

If you used an inner join instead, only people who have managers would be listed.

like image 4
Jirka Hanika Avatar answered Nov 07 '22 13:11

Jirka Hanika