CODEWITHSUNDEEP
sqljoin
Jacob Van Halteren
Jacob Van Halteren

Reputation: 21

SQL joins from same table column

Have two tables

I need to show employee id, employee name, manager name, and manager id, yet I cannot figure out how to show manager name. Here is what I have even though it is very wrong and doesn't show the real manager name

SELECT e.last_name "Employee", e.employee_id "Emp#", e.last_name "Manager",      
       d.manager_id "Mgr#"
FROM employees e, 
     departments d;

Upvotes: 1

Views: 49

Answers (2)

Redz Ch
Redz Ch

Reputation: 33

you can do inner join on manager_id so only the matching rows remain 

 SELECT last_name as "Employee", employee_id as "Emp#", last_name as "Manager", 
   manager_id as "Mgr#"
   FROM employees as e1
   INNER JOIN departments as d2 
   ON e1.manager_id = d2.manager_id;

Upvotes: 0

Jacob Van Halteren
Jacob Van Halteren

Reputation: 21

Figured it out using keyword Join - Only required use of one table twice

SELECT e.last_name "Employee", e.employee_id "Emp#", e2.last_name "Manager", 
       e.manager_id "Mgr#"
       FROM employees e
       JOIN employees e2 ON e2.employee_id = e.manager_id;

Or without Keyword Join / using a simple join

SELECT e.last_name "Employee", e.employee_id "Emp#", e2.last_name "Manager", 
   e.manager_id "Mgr#"
   FROM employees e, employees e2
   WHERE e2.employee_id = e.manager_id

Upvotes: 1

Related Questions