• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

Fine records with duplicate ssn and date of birth but different record ids

I am stuck on this query which seems like it should be simple.
I have a table that has five columns:
member_id (PK)
last_name
first_name
social_security_no
birthdate

I am trying to find all records where a single person (same social_security_no and birthdate) has one or more different member_ids.
Any assistance is appreciated. Thanks.
0
c4e41961
Asked:
c4e41961
3 Solutions
 
dportasCommented:
SELECT *
FROM tbl t
WHERE EXISTS
 (SELECT 1
  FROM tbl
  WHERE social_security_no = t.social_security_no
   AND birthdate = t.birthdate
   AND member_id <> t.member_id);
0
 
joshbennerCommented:
Something like this:
SELECT
  *,
  COUNT(*)
FROM myTable
GROUP BY social_security_no, birthdate
ORDER BY COUNT(*) DESC

Open in new window

0
 
joshbennerCommented:
You could add a HAVING COUNT(*) > 1
0
 
MNelson831Commented:
Select * from MyTable where SSN in (

Select SSN from MyTable having Count(*) >1 group by SSN)
0
 
c4e41961Author Commented:
Thanks!  I appreciate your solutions!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now