Why does this sql select selects everything 3 times

I have a homework where I need to display every employee together with his manager`s id (it needs to also display employees with no manager). All of them are in a same table named employees. However when I run this command:

select first_name, last_name, manager_id
from employees
connect by prior employee_id = manager_id;

​x
 
select first_name, last_name, manager_idfrom employeesconnect by prior employee_id = manager_id;​

I get 269 rows, which makes no sense to me as there are only 93 employees.

I tried using select distinct which works for me, but I want to know why does this display 269 rows. Also, is it possible to do this via recursive join somehow?

Answer

I think you need to use LEFT JOIN as follows:

SELECT
    EMP.*, MGR.*
FROM
    EMPLOYEES EMP
    LEFT JOIN EMPLOYEES MGR
    ON MGR.EMPLOYEE_ID = EMP.MANAGER_ID;

 
SELECT    EMP.*, MGR.*FROM    EMPLOYEES EMP    LEFT JOIN EMPLOYEES MGR    ON MGR.EMPLOYEE_ID = EMP.MANAGER_ID;​

Advertisement

Answer