CODEWITHSUNDEEP
t-sqlself-join
CodeMonkey
CodeMonkey

Reputation: 1107

Remove Duplicates from Employees Self Join

I have an employees table where all employees are located. I need to extract a subset of the employees with their corresponding supervisor. The table looks similar to this:

Emp_id | F_name | L_name | Superv_id  | Superv_flg
---------------------------------------------------
123      john      doe      456          N
456      jane      doe      278          Y
234      Jack      smith    268          N

My query looks like this so far:

with cte as 
(
    select f_name + ' ' l_name as supervisor, superv_id, emp_id
        from [dbo].[SAP_worker_all]
        where supvr_flag = 'Y'
    )
    SELECT distinct w.[first_name]
           ,w.[last_name]
           ,cte.supervisor
FROM [dbo].[SAP_worker_all] w
join  cte
        on w.[superv_id] = cte.[superv_id];

I am getting duplicate values and the supervisors returned are not the correct values. What did I do wrong?

Upvotes: 0

Views: 73

Answers (1)

paparazzo
paparazzo

Reputation: 45096

if empID is unique you should not have duplicates 

SELECT w.*, s.*
 FROM [SAP_worker_all] w 
 JOIN [SAP_worker_all] s 
   ON s.[Emp_id] = w.[Superv_id] 
  AND s.[Superv_flg] = 'Y'

Upvotes: 1

Related Questions