Syntax for opposite of "IN"

[DaGeek]

Sorry for being dumb, but I just can't figure this one out....

 

I have ONE table - categories.

 

It has two fields

 

itemno, category

 

An item number can have an entry for many categories, ie

 

Item #1, Category A

Item #1, Category B

 

Item #2, Category A

Item #2, Category B

Item #2, Category C

 

Item #3, Category B

Item #3, Category C

 

 

If I want a list of ALL items that are in Categroy B AND in Category C I would expect to get back just Item Numbers 2 and 3 because Item #1 does not have an entry for category C.

 

If I use "select * from categories where category in ('B','C')", I will also get Item #1 which I do NOT want.

 

 

Does anyone have a suggestion as to the syntax for returning all rows where there is an entry for B AND C?

 

TY Brian

[PFMaBiSmAd]

Check out my post at this link and the other thread that is linked to - http://www.phpfreaks.com/forums/index.php/topic,200503.0.html

 

You can use a HAVING cnt = 2 (cnt is the alias name for the count(*) value) to find things that are in exactly two different categories, or you can use HAVING cnt > 1 to find everything that appears in more than just one category.