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:
The rows 4,5 & 7 in bold and italic are what I am after.
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'
AND t2.oranges = 'p'? I think this isn't what the op wants
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.
