Jacob Alley Jacob Alley - 18 days ago 4x
SQL Question

filtering out duplicates in rows based on columns

say i have 2 columns:

Fruit Condition
apple unripe
banana ripe
apple ripe
banana moldy
peach moldy
peach ripe
apple ripe
pineapple soldout

and i only want to know which fruit are either Ripe or Unripe and not moldy or sold out (only apple)

Select fruit
from example
where (condition <> 'moldy' or condition <> 'soldout')
and (condition = 'ripe' or condition = 'unripe')
group by fruit

is not working


You are using or in a not. This is the wrong approach for this.


where not (condition = 'moldy' or condition = 'soldout')

or use

where (condition <> 'moldy' and condition <> 'soldout')

Then, I assume you want the fruits that are ONLY ripe or unripe.

select distinct Fruit
from Example E1
where Condition in ('ripe','unripe')
and not exists 
    select E2.Fruit 
    from Example E2 
    where E1.Fruit = E2.Fruit
    and E2.Condition in ('moldy','soldout')