Assume a table list this:
Category, Product
Round , Tire
Rubber, Tire
Black, Tire
Round, Ball
Rubber, Eraser
Green, Grass
I would like a list of all Products that are members of a arbitrary list of one or more Categories, for example Products that are Round, Rubber, and Black (i.e., Tire)
Just can't figure it out, so I though my problem was design, and I thought about how to do this If instead I had a Product Table, a Category Table and a ProductCaterogy Table
Product
ID
Name
Cateogory
ID
Name
ProductCategory
ProductID
CategoryID
Still I can't see how in a simple query to get the desired results, I think I am just hitting a wall, this has to be easy?
SELECT DISTINCT Product FROM Table WHERE Category IN ('Round', "Rubber", "Black") returns products that are any of the categories.
SELECT Product FROM Table where Category="Round" AND Category="Rubber" AND Category="Black" returns nothing (of course).
So some sort of nested query?