Duplicate Records Query

[jestxstate]

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

[jordansparks]

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.

[jestxstate]

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.