CODEWITHSUNDEEP
mysql
user1800522
user1800522

Reputation: 23

mysql find duplicates


id | apples  | oranges
1    111       p
2    112       p
3    113       p
4    113       f
5    113       -
6    114       p
7    114       f
8    115       -
9    115       -
10   116       f

Looking for a way to return only the rows where:

  1. oranges contains value 'f' or no value (empty)
  2. there are duplicate values in apples
  3. at least one duplicate in applies contains value of 'p' in oranges.

The rows 4,5 & 7 in bold and italic are what I am after.

Upvotes: 2

Views: 303

Answers (2)

Woot4Moo
Woot4Moo

Reputation: 24316

Lets write these as three separate queries and then combine them.

Looking for a way to return only the rows where oranges contains value 'f' or no value (empty)

select *   
from table  
where oranges = 'f'
or oranges is null;

where there are duplicate values in apples and at least one duplicate contains value of 'p' in oranges

select * 
( 
  select *
  from table    
  INNER JOIN (SELECT apples FROM inner_table
  GROUP BY apples HAVING count(id) > 1)
)
where oranges ='p'

And then you can combine them like so: 

select *
(
    select *  
    from table  
    where (oranges ='f' or oranges is null)  
    INNER JOIN (SELECT apples FROM inner_table
  GROUP BY apples HAVING count(id) > 1)
)
where oranges ='p'

This is untested since I don't have a schema to work from.

Upvotes: 0

lc.
lc.

Reputation: 116438

You need to do a self-join. Something like this:

SELECT t1.*
FROM myTable t1
INNER JOIN myTable t2 ON t1.apples = t2.apples
WHERE t1.oranges IN ('f', '-')
AND t2.oranges = 'p'

SQL Fiddle example

Upvotes: 1

Related Questions