SQL joins from same table column

Question

Have two tables

employees: employee_id , employee_name, manager_id, department_id
departments: department_id,manager_id

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;

update your question add a proper data sample .. and the expected result — ScaisEdge
– ScaisEdge, Commented Oct 8, 2018 at 16:24
Google 'SQL joins' and follow the simple examples that will easily solve this problem for you. — Martin
– Martin, Commented Oct 8, 2018 at 16:25
Never use commas in the FROM clause. Always use proper, explicit, standard JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented Oct 8, 2018 at 18:13

Jacob Van Halteren · Accepted Answer · 2018-12-02 22:24:32Z

1

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

edited Dec 2, 2018 at 22:24

answered Oct 8, 2018 at 16:53

Jacob Van Halteren

215 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Barbaros Özhan Over a year ago

nice to solve on your own. If you need an extra column from the other table departments like dept_name, you may add that table with a left join, also.

Redz Ch · Accepted Answer · 2018-12-02 22:58:32Z

0

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;

answered Dec 2, 2018 at 22:58

Redz Ch

338 bronze badges

Collectives™ on Stack Overflow

SQL joins from same table column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related