Karina M Karina M - 12 days ago 7
SQL Question

Create a stored procedure that returns the manager of an employee

I have done the following to create a stored procedure that returns the manager of an employee in the EMPLOYEE table:

[Additionally: REPORTSTO is a NUMBER value and contains the ID of the manager of the employee]

create or replace PROCEDURE MANAGER_OF_EMPLOYEE
(
THE_EMPLOYEEID IN NUMBER
)
AS
TEMP VARCHAR2(20);
TEMP2 VARCHAR2(20);
TEMP3 VARCHAR2(20);
TEMP4 VARCHAR2(20);
BEGIN
SELECT MGR.FIRSTNAME, MGR.LASTNAME, EMP.FIRSTNAME, EMP.LASTNAME INTO TEMP, TEMP2, TEMP3, TEMP4
FROM EMPLOYEE EMP
LEFT OUTER JOIN EMPLOYEE MGR
ON EMP.EMPLOYEEID = MGR.REPORTSTO
WHERE EMP.EMPLOYEEID = THE_EMPLOYEEID AND EMP.REPORTSTO = MGR.EMPLOYEEID;
DBMS_OUTPUT.PUT_LINE(TEMP || ' ' || TEMP2 || ' IS THE MANAGER OF ' || TEMP3 || ' ' || TEMP4);
END MANAGER_OF_EMPLOYEE;


I always get 'Andrew Adams IS THE MANAGER OF Andrew Adams' no matter what I put as the parameter. Andrew Adams is the first row in the EMPLOYEE table and is the top manager so technically he IS his own manager. But why do I get the same output no matter what input I put?

Answer

You've gotten your join backwards. This:

LEFT OUTER JOIN EMPLOYEE MGR 
ON EMP.EMPLOYEEID = MGR.REPORTSTO

Should be this:

LEFT OUTER JOIN EMPLOYEE MGR 
ON MGR.EMPLOYEEID = EMP.REPORTSTO

Once you correct this, you don't need anything in your WHERE clause except the EMPLOYEEID = THE_EMPLOYEEID.

Also, I wouldn't use a left join here, unless you have empty values in the REPORTSTO field that you want to include. Just use an inner join.