I am trying to report on duplicate records in a single table which has a unique key of app_cao_number. The duplicates occur if either: 1. The Passport field is duplicated; 2. The ID field is duplicated, or; 3. The Surname+FirstName are duplicated.
I can do this easily enough with three passes of the table using ORDER BY. But I am hoping to use a single SELECT statement, with subqueries, to do the job.
Starting with just finding duplicate IDs I have the following statement:
SELECT app_cao_number, app_id, (SELECT app_id FROM people p2 WHERE p2.app_id IS NOT null AND p2.app_id <> '' AND p1.app_cao_number <> p2.app_cao_number AND p1.app_id = p2.app_id GROUP BY p2.app_id) AS DupId FROM people p1 WHERE app_id IS NOT null AND app_id <> ''
This appears to get me the results that I want, but also include rows that have a null DupId - despite my attempts to ignore blank and null values in the SELECT statement. Once this works I should be able to expand it to include the passport and name checks.
Please can someone explain why I have the following data output with nulls in the DupId column? Thank you.
Further: I thought it might be the GROUP BY clause, but I replaced it with a DISTINCT clause (below), but this gave the same result.
(SELECT DISTINCT p2.app_id FROM people p2 WHERE p2.app_id IS NOT null AND p2.app_id <> '' AND p1.app_cao_number <> p2.app_cao_number AND p1.app_id = p2.app_id ) AS DupId