Jacob Alley Jacob Alley - 1 year ago 78
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 Source

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')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download