I am working with MySQL database with around 30,000 records and trying to come up with a query that filters out certain types of duplicate records.
There are two relevant columns - email and flag. I need to get all records that do not have a duplicate email. For the records that have duplicate email, I exclude them only if for ALL those duplicates, the flag is set to 'no'. If, for one of those rows, the flag is 'yes', then I need to return all of those records.
So far, I have this: http://sqlfiddle.com/#!2/ac20d/6 It currently returns rows 3,4,5,6,7,11,12. I need it to return 3,4,5,6,7,8,9,11,12.
Any help shall be appreciated!
Thanks!
I do not understand why you don't return 10 in the result. It follows your rules but it's an empty string. So, I've come up with this:
select * from mytable where email IN (
select
email
from
mytable t
group by email
having count(email) = 1 or EXISTS (SELECT 1 FROM mytable st
WHERE t.email = st.email
AND st.flag = 'yes')
)
AND email != ''
Is it what you need?