Forum Moderators: open
I have a database that consists of three tables: Products, Dyes, and ProductDyes. Each product can be dyed with up to six dyes. The Dyes table contains all the possible dye colors (fields DyeID (index) and Color (text)). The ProductDyes table just matches up which products use which dyes (fields ProductID and DyeID). So, for example if a particular product uses three dye colors, it would have three records in the ProductDyes table. So far so good.
I need to come up with a SELECT statement that returns only products that use a particular combination of dyes. For example, I might want to find all products that use both green and blue dyes. Say the DyeIDs for these colors are 1 and 2 respectively. If I use
SELECT Products.ProductID, Name, DyeID FROM Products, ProductDyes WHERE Products.ProductID=ProductDyes.ProductID AND DyeID=1 AND DyeID=2of course I get no results returned.
On the other hand, if I use
SELECT Products.ProductID, Name, DyeID FROM Products, ProductDyes WHERE Products.ProductID=ProductDyes.ProductID AND DyeID=1 OR DyeID=2then I get a row for every product that contains either blue or green dye, which again is not what I want. What I do want is a single row returned for each product that contains both blue and green dyes.
Again please forgive a rank newbie question. TIA.
SELECT ProductID, Name
FROM Products
WHERE ProductID IN (SELECT ProductID
FROM ProductDyes
WHERE DyeID =1)
AND ProductID IN (SELECT ProductID
FROM ProductDyes
WHERE DyeID =2)
SELECT
Products.ProductID, Name
FROM
Products, ProductDyes, ProductDyes as ProductDyes2
WHERE
Products.ProductID = ProductDyes.ProductID
AND Products.ProductID = ProductDyes2.ProductID
AND ProductDyes.DyeID = 1
AND ProductDyes2.DyeID = 2;
