SQL left outer joins

Question

So I have one table which has employee number (empno), name(ename) and manager number(mgr).
The question: List the names of all employees along with the name of their manager and the name of their manager's manager, with a blank at any left if a manager does not exist.

What I have so far:
SELECT a.ename, b.ename, b.mgr FROM emp a LEFT OUTER JOIN emp b ON a.mgr=b.empno

This shows the employee, their manager, and their manager's number... Anyone able to help me change the manager number in the third column into a name?

score 1 · Accepted Answer · answered Jun 07 '13 at 15:56

You're already most of the way there, you want to do the same thing you did in your first join.

SELECT a.ename, 
       b.ename, 
       c.ename 
FROM emp a 
 LEFT OUTER JOIN emp b 
     ON a.mgr=b.empno;
 LEFT OUTER JOIN emp c
     ON b.mgr=c.empno

Note: I'd use a bit more friendly names for your aliases. a, b, c are pretty confusing.

SQL left outer joins

1 Answers1