Solved

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

Posted on 2009-04-13
5
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 22

Accepted Solution

by:
dportas earned 150 total points
ID: 24133153
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
ID: 24133160
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
ID: 24133162
You could add a HAVING COUNT(*) > 1
0
 
LVL 15

Assisted Solution

by:MNelson831
MNelson831 earned 50 total points
ID: 24133174
Select * from MyTable where SSN in (

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

Author Closing Comment

by:c4e41961
ID: 31569689
Thanks!  I appreciate your solutions!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

623 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