How to correctly SELF JOIN tables in oracle 11g?

Question

I want to refer the employees to their manager.

I got two examples here. Both of them give me a solution that I don't want.

First self join query:

select m1.naam as medewerker, m2.naam as manager
from medewerkers m1, medewerkers m2
where m1.medewerker_id = m2.manager
order by 1;

Screenshot of solution:

now I am only getting all the managers and not the employees

Second self join query:

select b.naam as employee, a.naam as manager
from medewerkers a right outer join medewerkers b on(a.medewerker_id = b.manager);

Screenshot of solution:

This will result in the same as above with the extra employees. The problem is that the employees are not referred to their managers (managers are still referred to their self).

At the moment this is what I insert into my DB. Maybe there could be a problem with my insert value. Because the given awnsers won't work

--Employee

insert into MEDEWERKERS (medewerker_id, naam, adres, telefoon_nummer, salaris, functie, werknemer_winkel_nummer, manager)
values(11111112, 'Joost', 'Eindhoven Langloopstraat 1', 0678765478, 1500, 'baliemedewerker', 10, null);
insert into MEDEWERKERS (medewerker_id, naam, adres, telefoon_nummer, salaris, functie, werknemer_winkel_nummer, manager)
values(11111119, 'Rick','Gemert. Dunneweg 76', 0678768315, 2200, 'Manager', 10, 11111119);

Answer 1

You're either got the join backwards or pulling from the wrong sides (depending on how you look at it). If you use different table aliases it becomes more apparent:

select emp.naam as medewerker, man.naam as manager
from medewerkers emp, medewerkers man
where emp.medewerker_id = man.manager   --asking for rows where the employee is the manager's manager!
order by 1;

change your join order (and use proper join syntax):

select emp.naam as medewerker, man.naam as manager
from medewerkers emp
INNER JOIN medewerkers man
    ON emp.manager = man.medewerker_id 
order by 1;

Here's the proper statements to fix your bad input data:

insert into MEDEWERKERS 
    (medewerker_id, naam, adres, telefoon_nummer, salaris, functie, werknemer_winkel_nummer, manager)
values
    (11111112, 'Joost', 'Eindhoven Langloopstraat 1', 0678765478, 1500, 'baliemedewerker', 10, 11111119);

insert into MEDEWERKERS 
   (medewerker_id, naam, adres, telefoon_nummer, salaris, functie, werknemer_winkel_nummer, manager)
values
    (11111119, 'Rick','Gemert. Dunneweg 76', 0678768315, 2200, 'Manager', 10, null);

Answer 2

Is the problem the Rick/Rick line and so on? If so, you can replace the manager name with NULL by fixing the on clause:

select e.naam as medewerker, m.naam as manager
from medewerkers e left join
     medewerkers m
     on m.medewerker_id = e.manager and
        m.medewerker_id <> e.medewerker_id
order by 1;

Or, because the problem might be duplicates in the original table, you might want:

select e.naam as medewerker, m.naam as manager
from medewerkers e left join
     medewerkers m
     on m.medewerker_id = e.manager
where e.medewerker_id <> e.manager     
order by 1;