Help With Nested Query

I am having trouble with the following query.

Important Tables:
Product (table of products)
--ProductID
--ProductName

ProductCategories (Associates a Product with one or more categories)
--ProductID
--CategoryID

Category (table of categories that a product may fall under)
--CategoryID
--CategoryName

Information:

Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.

Attempted Solution:
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
AND
(ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))

This returned zero records though it should have returned the product that is in categories 1&2.

I would appreciate any help available.

Thank you,
-PatrickI am trying to create a query that will find all products that are in categories 1 & 2.do a regular many-to-many join, but use GROUP BY on the product, and HAVING to retain only those products which were in more than one category
select ProductName
from Category C
inner
join ProductCategories PC
on C.CategoryID = PC.CategoryID
inner
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
group
by ProductName
having count(*) > 1|||You are going to kick yourself, but the reason your query failed to return records is because you were trying to compare outer "ProductID"s to inner "CategoryID"s.

...WHERE (ProductID IN (SELECT CategoryID...???

You can rewrite your query more simply like this:

select Product.*
from Product
inner join ProductCategories Cat1 on Product.ProductID = Cat1.ProductID
inner join ProductCategories Cat2 on Product.ProductID = Cat2.ProductID
where Cat1.CategoryID = 1 and Cat2.CategoryID = 2

Use the DISTINCT keywork if the query returns multiple records.