CODEWITHSUNDEEP
sqloracle-database
Bilal Abdulaal
Bilal Abdulaal

Reputation: 135

How to get the duplicate names of employees who have multiple employee numbers

I'm using Oracle 10g.

If i have the following duplicate rows (Same Employee with two Employee numbers):

Employee_No      Employee_Name      ID_NO
----------------------------------------------
0002345          John Debb          100345642
0030988          John Debb          100345642
----------------------------------------------

i want to get the result as:

Employee_No_1      Employee_No_2     Employee Name    ID_NO
----------------------------------------------------------------
0002345            0030988           John Debb        100345642
----------------------------------------------------------------

Is it possible to be done in SQL? or it needs PL/SQL? and what would the query be?

Upvotes: 1

Views: 7668

Answers (3)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Not quite in the format requested, but this will handle the case where there could be more than just 2 duplicates.

SELECT e.Employee_No, e.Employee_Name, e.ID_NO
    FROM (SELECT Employee_Name, ID_NO
              FROM Employee
              GROUP BY Employee_Name, ID_NO
              HAVING COUNT(*) > 1) q
        INNER JOIN Employee e
            ON q.Employee_Name = e.Employee_Name
                AND q.ID_NO = e.ID_NO
    ORDER BY e.Employee_Name, e.ID_NO, e.Employee_No

Upvotes: 3

Vivek Viswanathan
Vivek Viswanathan

Reputation: 1963

Query is as below,

select e1.employee_no, e2.employee_no, e1.employee_name, e1.id_no
from employee e1
join employee e2
    on e1.id_no = e2.id_no
where e1.employee_no < e2.employee_no

Upvotes: 1

JNK
JNK

Reputation: 65157

SELECT MIN(Employee_no), MAX(employee_no), Employee_name, id_no
FROM Employee
GROUP BY Employee_name, id_no
HAVING MIN(employee_no) <> MAX(employee_no)

I don't do Oracle, but I think this is pretty generic syntax that should work.

Upvotes: 6

Related Questions