CODEWITHSUNDEEP
mysqlsqljoin
PRM
PRM

Reputation: 51

How to use joins in MySQL in this case

I have an employee table and a department table. How to can I use join to get the required below result. manager_id in the employee table is nothing but the employee id. Please help me to find out the answer 

    Employee Table        

    id  | name  |   manager_id | department_id            
    ----------------------------------------              
    1   |  A    |   NULL       | 1                          
    2   |  B    |   1          | 2                        
    3   |  C    |   NULL       | 3                         
    4   |  D    |   3          | 2

     Department Table

     id    |   department_name
     -------------------------
     1    | Admin
     2    | HR
     3    | Finance 


    Required OutPut

    id  | name  |  manager_name   | department_name
    -----------------------------------------------
    1   |  A    |   NULL          | Admin                
    2   |  B    |   A             | HR                
    3   |  C    |   NULL          | Finance                
    4   |  D    |   C             | HR

Upvotes: 2

Views: 84

Answers (3)

vikas bhandari
vikas bhandari

Reputation: 488

This will help you.

SELECT e.id as id, e.name as name e.manager_id as manager_id, 
d.department_name as department_name
FROM Employee as e 
JOIN Department as d 
ON e.department_id = d.id

Upvotes: 0

Ishan Shah
Ishan Shah

Reputation: 1676

SELECT E1.id, E1.name, E2.name as manager_name, D1.department_name
FROM Employee E1
LEFT JOIN Employee E2 ON (E1.id = E2.manager_id)
JOIN Department D2 ON (E1.department_id = D1.id)

You need first do the self join to find the manager name and after that nee to join with the Department to find the appropriate Department name.

Upvotes: 1

asmgx
asmgx

Reputation: 8004

Use both Inner join and outer join

select E1.ID, E1.Name, E2.Name, D.department_name
FROM Employee E1
LEFT OUTER JOIN Employee E2 ON E2.ID = E1.manager_id
INNER JOIN Department D ON D.id = E1.department_id

Upvotes: 2

Related Questions