Combining DISTINCT and (opposite of DISTINCT) in a query

bunner_bob

I figured out how to retrieve results where a field value is the same in several records (even if I don't know what that value is) - which is sort of "the opposite of DISTINCT":

SELECT mlsNumber, productId, COUNT(*) AS count 
FROM orderItems 
WHERE customerId=6 AND mlsNumber IS NOT NULL AND productId IS NOT NULL AND (itemType='emailFlyer' OR itemType='printFlyer') 
GROUP BY mlsNumber, productId 
HAVING count = 2

Looking at the WHERE clause, this would give me a result if both items were email flyers or if both items were print flyers. What I want to do is only get a result if one item is a print flyer and the other item is an email flyer. Which is to say, I want DISTINCT itemType - though that syntax doesn't seem to cut it in the context of this query.

Anyone know how I might modify this to say "I only want one of each itemType"?

Thanks!

Lars_Berg

select mlsNumber, 
       productId
  from orderItems
 where customerId = 6
   and mlsNumber is not null
   and productId is not null 
   and itemType in ('emailFlyer','printFlyer')
 group by mlsNumber, 
       productId
having count(distinct itemType) = 2