MySQL - Find Multiple Entries, and Delete One based on the existance of another entry
I have a table "fruitlist" that contains just two fields, "color" & "fruit".
red : apple
green : apple
green : banana
yellow : banana
red : grapes
green : grapes
What I would like to do is delete the "GREEN" entry, ONLY IF the fruit has both a "GREEN" and a "RED" entry!
In the example entries above I would like to delete the "Green Apple" and the "Green Grapes", but leave the "Green Banana" alone.
I'm thinking I need to do an inner join, but not sure how to approach it.