How can I use SQL to select duplicate records, along with counts of related items?
- by mipadi
I know the title of this question is a bit confusing, so bear with me. :)
I have a (MySQL) database with a Person record. A Person also has a slug field. Unfortunately, slug fields are not unique. There are a number of duplicate records, i.e., the records have different IDs but the same first name, last name, and slug. A Person may also have 0 or more associated articles, blog entries, and podcast episodes.
If that's confusing, here's a diagram of the structure:
I would like to produce a list of records that match this criteria: duplicate records (i.e., same slug field) for people who also have at least 1 article, blog entry, or podcast episode.
I have a SQL query that will list all records with the same slug fields:
SELECT
id,
first_name,
last_name,
slug,
COUNT(slug) AS person_records
FROM
people_person
GROUP BY
slug
HAVING
(COUNT(slug) > 1)
ORDER BY
last_name, first_name, id;
But this includes records for people that may not have at least 1 article, blog entry, or podcast. Can I tweak this to fit the second criteria?
