[aj_lavaca]

Using Oracle SQL, I have a table that I am using that has (the important ones) employee_id, first_name, last_name, manager_id. The problem ask that we list the employee and that employee's manager. The manager_id is the employee_id of the person who is their manager. Here's an example of the table.

Emp_ID     First Name     Last Name     Manager ID
01         Anderson       Dooly         (null)
02         Logan          Masterson     01
...

From the last statement, I mean that the manager_id for Logan Masterson is 01, which would mean Anderson Dooly is his manager.

This is my code so far:

select e.employee_id, e.first_name, e.last_name, e.manager_id
from f_emp e;

Which pops out a similar table to the one listed above. I'm trying to figure out how to get the name of the manager in that slot instead of the manager's employee number. We've just started the chapter about inner/outer joins, but I cannot figure out how to do this from the books text, or know if I'm going in the right direction. Any guidance would be appreciated.
Also, I'm at work and cannot work on this here. I am using what I can remember from my code to try to give a good enough example to get me pointed where I need to go.

This post has been edited by aj_lavaca: 10 September 2012 - 01:07 AM

[rgfirefly24]

you would want to inner join back on the same table joining the manager_id to the employee_id. this will cause problems when the manager Id is null though because it won't have a joined record so will not show up in the final list.

SELECT <column list> 
from f_emp e 
inner join f_emp e2 on e.manager_id = e2.employee_id

This post has been edited by rgfirefly24: 10 September 2012 - 08:26 AM

[aj_lavaca]

I see now how that worked. I went ahead and used a left outer join to so that the president would be included. Thank you for getting me in the right direction. Here was the final code I used.

SELECT e.emp_id, e.last_name, e.manager_id,, m.last_name
FROM f_emp e LEFT OUTER JOIN f_emp m
ON e.manager_id = m.emp_id;