SQLTeam.com | Weblogs | Forums

Query returns a list of all items with a set of attributes


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




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?

/* data set up: btw, it's EXTREMELY helpful if you provide useable data like this 
in your initial q; it took more of my time to prep the data than to write the query. */
CREATE TABLE #product_categories (
    category varchar(30) NOT NULL,
    product varchar(30) NOT NULL
INSERT INTO #product_categories VALUES
('Category', 'Product'),
('Round', 'Tire'),
('Rubber', 'Tire'),
('Black', 'Tire'),
('Round', 'Ball'),
('Rubber', 'Eraser'),
('Green', 'Grass')

/* Actual Query; if the categories are known to be unique, you can change 
"COUNT(DISTINCT pc.category)" to "COUNT(*)" */
SELECT pc.product
FROM #product_categories pc
WHERE pc.category IN ('Black', 'Round', 'Rubber')
GROUP BY pc.product
HAVING COUNT(DISTINCT pc.category) = 3


Interesting....I would have never come up with that. Point taken about data setup!