Duplicate Records Query

For complex topics that regular users would not be interested in. For power users and database administrators.
Post Reply
jestxstate
Posts: 3
Joined: Thu Jul 26, 2007 8:46 am
Location: St. David's Community Health Foundation

Duplicate Records Query

Post by jestxstate » Thu Jul 26, 2007 8:59 am

I'm trying to write a query to identify duplicate patient records.
Basically I want to pull patients from the same table that have the same fname, lname, and DOB but have different unique ids.
My first try was:

select d2.patnum as id,d2.patnum,d2.lname,d2.fname,d2.birthdate from patient as d1, patient as d2 where d1.lname=d2.lname and d1.fname=d2.fname and d1.birthdate=d2.birthdate and d1.patnum!=d2.patnum order by d2.lname, d2.fname

Which didn't work because it includes deleted patients so I added "and d2.patstatus != 4" at the end (before order by) which got rid of deleted patients but not the matching record of the deleted patient.

I figured what I needed to do was create a temp table of "non-deleted" patients to draw from so I wrote:

select d2.patnum as id,d2.patnum,d2.lname,d2.fname,d2.birthdate from (select * from patient where patient.patstatus !=4) as d1, (select * from patient where patient.patstatus !=4) as d2 where d1.lname=d2.lname and d1.fname=d2.fname and d1.birthdate=d2.birthdate and d1.patnum!=d2.patnum order by d2.lname, d2.fname

Which caused Opendent to close down all together. :?

Can someone help with this problem

User avatar
jordansparks
Site Admin
Posts: 5739
Joined: Sun Jun 17, 2007 3:59 pm
Location: Salem, Oregon
Contact:

Post by jordansparks » Thu Jul 26, 2007 10:53 am

Are you using Oracle or MySQL?

Try:
SELECT COUNT(*) AS repetitions, LName, FName, Birthdate
FROM patient
WHERE PatStatus!=4
GROUP BY LName,FName,Birthdate
HAVING repetitions > 1

The reason I ask about Oracle is that, when you use a GROUP BY clause, it might require you to include all 4 columns (??). So I don't know how it handles the repetitions peudocolumn. But I tested it on my computer with MySQL and it works.
Jordan Sparks, DMD
http://www.opendental.com

jestxstate
Posts: 3
Joined: Thu Jul 26, 2007 8:46 am
Location: St. David's Community Health Foundation

Post by jestxstate » Thu Jul 26, 2007 2:58 pm

Thanks, and yes we are still using MySQL for now. I think I'm going to really miss it when we have to switch to Oracle.

Post Reply