SQL - Remove Duplicate With Inner Join

Create Table 

CREATE TABLE e (Emplyee_Dep_ID INT, Employee_Reg_Date DATE, Employee_Rep_Manager VARCHAR(100), Employee_Status INT, Email  VARCHAR(100));

INSERT INTO e

VALUES

(1,'2020-01-01',' Robot', 3,'a@a.com'),

(1,'2020-01-01',' Robot', 3,'b@a.com'),

(1,'2020-01-01',' Robot', 3,'c@a.com'),

(1,'2020-01-01',' Robot', 3,'d@a.com'),

(1,'2020-01-01',' Robot', 1,'e@a.com');

CREATE TABLE s  (Status_ID int, Status_Code varchar(100));

INSERT INTO s

VALUES

(3,'Promoted'),

(1,'Probation');

Remove Duplicate

SELECT *

from

(

    SELECT  emp.emplyee_dep_id,emp.employee_reg_date,emp.employee_rep_manager,

            employee_status as status_id

    FROM e emp

    INNER JOIN (SELECT

                    emplyee_dep_id,employee_reg_date,employee_rep_manager, 

                         COUNT(*) AS CountOf

                    FROM e

                    GROUP BY emplyee_dep_id,
                             employee_reg_date,
                             employee_rep_manager

                    HAVING COUNT(*)>1

                ) emp1

                ON emp.emplyee_dep_id=emp1.emplyee_dep_id

                AND emp.employee_reg_date=emp1.employee_reg_date

                AND emp.employee_rep_manager = emp1.employee_rep_manager

) E

INNER JOIN s s

ON E.status_id = s.status_id

where s.status_code = 'promoted'

Group by emplyee_dep_id,employee_reg_date,employee_rep_manager