CODEWITHSUNDEEP
sqloracle-database
sandeep
sandeep

Reputation: 53

sql not in and in oporators

HI I am unable to filter materials that belong to only specific list.

Select material 
from price 
where region='04' 
  and pricelist ='5' 
  and pricelist not in ('4','6','7');

I want only those materials which are unique only in pricelist 5 and not in any other pricelists. how can i get this?

Upvotes: 1

Views: 49

Answers (2)

sgeddes
sgeddes

Reputation: 62851

Your current query will only return results where pricelist = 5 -- the not in statement is irrelevant unless it exclude that record. It sounds like you want to return any material which matches on the 5, but doesn't have any other non-5 matches.

One option is to use exist. Here's another option using conditional aggregation with max and case which eliminates the need for multiple queries:

select material 
from (
  select material,
    max(case when pricelist = '5' then 1 else 0 end) haspl5, 
    max(case when pricelist != '5' then 1 else 0 end) hasothers
  from price 
  where region='04' 
  group by material
) t
where haspl5 = 1 and hasothers != 1

Upvotes: 0

David Faber
David Faber

Reputation: 12485

You might try something like this (alternately, you could do a self-join, but I think this is just as easy, if not easier, to understand):

SELECT material
  FROM price a
 WHERE region = '04'
   AND pricelist = '5'
   AND NOT EXISTS ( SELECT 1 FROM price b
                     WHERE b.material = a.material
                       AND b.region = a.region
                       AND b.pricelist != a.pricelist )

What the above will do will find values of material in region '04' where the pricelist is '5' while excluding those same materials in the same region but on a different price list.

Upvotes: 1

Related Questions