displaying non-duplicate keys of all duplicate entries

Disson · #1 (**permalink**) 03-30-04, 17:55

Ok, so I'm checking for duplicate data in a table. Let's say it has 3 data fields and a key field (i.e. "ID", "FIRST", "MIDDLE", "LAST"). No keys are duplicated. If I find entries that have the same data in each of the non-key fields, I want to know the keys for all those entries. I have been able to find duplicate rows using this...

Code:

SELECT
    TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
FROM
    TABLE
GROUP BY
    TABLE."FIRST", TABLE."MIDDLE", TABLE."LAST"
HAVING
    COUNT(*) > 1

Unfortunately I've found no way to incorporate the return of the TABLE."ID" for every duplicated entry. Is there some way I can join the result with the db.table to find this, or some other way to make this happen?

Thanks,
Dean

Pat Phelan · #2 (**permalink**) 03-30-04, 18:06

Sure, use:

PHP Code:


			
SELECT

    A."ID", A."FIRST", A."MIDDLE", A."LAST"

FROM TABLE AS A

WHERE 1 < (SELECT Count(*)

   FROM TABLE AS B

   WHERE  B."FIRST" = A."FIRST"

      AND   B."LAST" = A."LAST"

      AND   B."MIDDLE" = A."MIDDLE")

-PatP