retrieving the duplicate SSN from the huge number of employess all of them

Question

I have written this query for getting the employees with same SSN numbers. But this query is giving me the all the employess, can't figure out whats wrong with it.

SELECT a.empid, 
       a.NAME, 
       a.ssn 
FROM   p_data a 
WHERE  ssn IN (SELECT ssn 
               FROM   p_data b 
               WHERE  b.ssn = a.ssn 
               GROUP  BY ssn 
               HAVING ( a.ssn ) > 1); 

Answer 1

You want groups with more than one record(duplicates), so use

HAVING COUNT( * ) > 1); 

instead of

HAVING ( a.ssn ) > 1); 

Another approach which should be more efficient:

SELECT a.empid, 
       a.NAME, 
       a.ssn 
FROM   p_data a 
WHERE EXISTS
(
    SELECT 1 FROM p_data a2
    WHERE  a.empid <> a2.empid
    AND    a.ssn = a2.ssn 
)

This also works if SSN is nullable. Then you just have to change the last line to:

AND COALESCE(a.ssn, '') = COALESCE(a2.ssn, '')