Return records with the same social security number

23 Mar 2006 5:07 PM

Anonymous

I have a table with FName, LName, SocialSecurity. I want to return the
records where the social security numbers are the same showing FName, LName,
SocialSecurity.

I did a count on SocialSecurity to find the numbers where there was more
than 1 record but I don't know how to return the FName, LName, and
SocialSecurity where the count > 1.

Thanks!

23 Mar 2006 5:32 PM

Jim Underwood

Untested...

select FName, LName, SocialSecurity
from SomeTable A
where SocialSecurity in (
    select b.SocialSecurity from SomeTable B
    group by b.SocialSecurity
    having count(b.SocialSecurity) > 1
)

Show quote

"Anonymous" <Anonym***@discussions.microsoft.com> wrote in message
news:BE5F5193-5BD3-4F5D-B898-C80B352C7749@microsoft.com...
> I have a table with FName, LName, SocialSecurity. I want to return the
> records where the social security numbers are the same showing FName,
LName,
> SocialSecurity.
>
> I did a count on SocialSecurity to find the numbers where there was more
> than 1 record but I don't know how to return the FName, LName, and
> SocialSecurity where the count > 1.
>
> Thanks!

23 Mar 2006 5:43 PM

Anonymous

Figured it out:

SELECT     FirstName,
    LastName,
    SSNumeric
FROM tblName DI
WHERE DI.SSNumeric <> '' And
      DI.SSNumeric IN
    (SELECT DI2.SSNumeric
    FROM DataImport DI2
         GROUP BY DI2.SSNumeric
         HAVING count(*) > 1)
ORDER BY SSNumeric

Show quote

"Anonymous" wrote:

> I have a table with FName, LName, SocialSecurity. I want to return the
> records where the social security numbers are the same showing FName, LName,
> SocialSecurity.
>
> I did a count on SocialSecurity to find the numbers where there was more
> than 1 record but I don't know how to return the FName, LName, and
> SocialSecurity where the count > 1.
>
> Thanks!