Posted by MC on 01/19/06 11:09

try this one:

select test.Name, test.SSN
from
test
inner join (select SSN from test group by SSN having count(*)>1) t2 on
test.ssn = t2.ssn

<gwhite1@kc.rr.com> wrote in message
news:1137647549.440302.217140@g49g2000cwa.googlegroups.com...
>I have a table like this:
>
> Name, SSN
> Joe Smith, 1111
> Tom Why, 2222
> Larry Sam, 3333
> Paul Tom, 4444
> Steve bob, 1111
>
> I want a query to pull off
>
> Joe Smith, 1111
> Steve bob, 1111
>
> because someone accidently put in two different names with the same
> SSN. There should only be one 1111 in the SSN field in the whole
> database. I want to pull the duplicate SSN with the name. How can one
> query do this. I can write a VB program to do it but I think a query
> should work.
>
> I know how to do this:
>
> SELECT SSN, COUNT(*) AS cnt
> FROM test
> GROUP BY fSSN
> HAVING (COUNT(*) > 1)
>
> to find the duplicate SSN but I need the name listed with the SSN also.
> Any help?? Thanks!!
>
> Sheila
>

[Back to original message]