Jacob Alley Jacob Alley - 2 months ago 6
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

Answer

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

Use:

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')
    )