Find Duplicates in one field, but not in two.

[Moebius01]

This is a bit odd to explain, but I'm looking to find records that have a one field that's duplicated, while a second field is different. It's proprietary data, so I can't post the exact thing, but here's an example:

EmpID   EmpName     IncidentID
111     Bob Smith   123
111     Bob Smith   212
222     Tom Jones   341
111     Jim Hart    635
111     Bob Smith   781

In this case, I need to return Employees with different names sharing the same ID, Bob and Jim in our example. Since the table can store multiple records for the same employee, the usual count/group by approach isn't working for me. I know I've dealt with something like this in the past, but for the life of me can't remember how. It's doubly bad as I'm already on brain fry shifting back to Oracle after several years gone.

Can someone point me back in the right direction?

 

[Dagon]

There are a number of approaches you could use, but here are a couple:

create table dups values (id number, name varchar2(30), inc_id number)

insert into dups values (111,     'Bob Smith',   123);
insert into dups values (111,     'Bob Smith',    212);
insert into dups values (222,     'Tom Jones',    341);
insert into dups values (111,     'Jim Hart',     635);
insert into dups values (111,     'Bob Smith',    781);

Method 1:

select id, count(*)
from 
(select distinct id, name from dups)
group by id
having count(*) > 1

        ID   COUNT(*)
---------- ----------
       111          2

1 row selected.

Method 2:

select distinct id, name
from
(
select name, id, count(distinct name) over (partition by id) as name_count
from dups)
where name_count > 1

        ID NAME                          
---------- ------------------------------
       111 Bob Smith                     
       111 Jim Hart                      

2 rows selected.

 

[ChrisHunt]

Another way

SELECT *
FROM   employees e
WHERE EXISTS (SELECT 99
              FROM   employees e2
              WHERE  e2.id = e.id
              AND    e2.EmpName != e.EmpName)

It might not be the most efficient approach though.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd