In short, I have have a table foods that has duplicates on occasion. I have another associated table food_tags that has tags associated with the foods listed in the foods table.
Objective: I want to retrieve the records from the food_tags table for all duplicates found in the foods table except for the first records in the foods table (the records with the lowest id value).
SQL Fiddle - http://sqlfiddle.com/#!9/ee1c6/2
I have a working example of what I want but I'm almost certain this is not the best way to do it. Here's what I have:
SELECT
*
FROM
`food_tags`
WHERE
`food_id`
IN (
SELECT
`id`
FROM
`foods`
WHERE
`id`
NOT IN (
SELECT
MIN(`id`)
FROM
`foods`
GROUP BY
`name`
)
)
Basically I want to exclude the first record of the duplicates in the foods table (the one with the lowest id), but retrieve all the remaining duplicates and then get the results from the food_tags table based on the food_id of the duplicates.
I'm very curious to see what your ideas are on how to solve this problem and the best way to approach it based on the constraints.
Additional Information: Ultimately the goal is to delete these records, along with other records in other tables based on the same criteria. I realize that there are ways you can setup the schema or normalize the database to handle duplicate entries and automatic deletions of records from one table based on deletions in another related table. For the sake of the issue, let's assume that these options are not available.
Thanks in advance for your time and insight.
