Product (table of products)
ProductCategories (Associates a Product with one or more categories)
Category (table of categories that a product may fall under)
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.
SELECT * FROM Product
WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1))
(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 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
from Category C
join ProductCategories PC
on C.CategoryID = PC.CategoryID
join Product P
on PC.ProductID = P.ProductID
where C.CategoryID in (1,2)
having count(*) > 1