Solved

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

Posted on 2009-04-13
5
307 Views
Last Modified: 2012-05-06
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
Comment
Question by:c4e41961
5 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 150 total points
Comment Utility
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
 
LVL 1

Assisted Solution

by:joshbenner
joshbenner earned 50 total points
Comment Utility
Something like this:
SELECT

  *,

  COUNT(*)

FROM myTable

GROUP BY social_security_no, birthdate

ORDER BY COUNT(*) DESC

Open in new window

0
 
LVL 1

Expert Comment

by:joshbenner
Comment Utility
You could add a HAVING COUNT(*) > 1
0
 
LVL 15

Assisted Solution

by:MNelson831
MNelson831 earned 50 total points
Comment Utility
Select * from MyTable where SSN in (

Select SSN from MyTable having Count(*) >1 group by SSN)
0
 

Author Closing Comment

by:c4e41961
Comment Utility
Thanks!  I appreciate your solutions!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now