Query to Id referencing to an ‘Id’ to the same table

I have a table AMZ_EMPLOYEE_DETAILS with Employee Id, Employee Name and Supervisor1 and Supervisor 2. The Supervisors are employee itself but represented by their name. My task was to replace the Supervisor names with their Ids.

I have used the following query to obtain the solution but it uses sub-queries and the query does look optimized. For this I have also made a dimension table with all the Id and Employee Names ,table AMZ_EMPLOYEE

SELECT S1.ID, S1.EMP_NAME, S1.SUPER_1_NEW, ZZ.ID AS SUPER_2_NEW
FROM 
(SELECT A.ID,A.EMP_NAME,A.SUPER_1,A.SUPER_2,Z.ID AS SUPER_1_NEW 
FROM AMZ_EMPLOYEE_DETAILS A
LEFT JOIN 
AMZ_EMPLOYEE Z
ON A.SUPER_1 = Z.EMP_NAME ) S1
LEFT JOIN 
AMZ_EMPLOYEE ZZ
ON S1.SUPER_2 = ZZ.EMP_NAME 
ORDER BY 1

The following is the expected output.

Answer

The logic to use two self-LEFT JOIN is correct. You don’t need to use subqueries though. Consider:

SELECT a.id, a.emp_name, a1.id, a2.id
FROM amz_employee_details a
LEFT JOIN amz_employee a1 ON a1.emp_name = a.super_1
LEFT JOIN amz_employee a2 ON a2.emp_name = a.super_2