Product/Category/ProductCategory scenerio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Product/Category/ProductCategory scenerio

Author

Topic

MDCas
Starting Member

5 Posts

Posted - 2008-03-26 : 12:52:46

Here is my problem, I have three simple tables

Products
Categories
ProductCategories

Each product can be assigned to more than one category, hence the link table ProductCategories

Let's assume I have these categories in the category table
-Stationary
-Office
-Scool

I need an SQL query that enables me to find a list of products that has been assigned to the category "Stationary" AND "Office"

not either, but both.

How do I go about this? Thanks a lot in advance :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-26 : 13:00:41

[code]SELECT pc.ProductID
FROM Categories AS c
INNER JOIN ProductCategories AS pc ON pc.CategoryID = c.CategoryID
WHERE c.CategoryName IN ('Stationary', 'Office')
GROUP BY pc.ProductID
HAVING COUNT(DISTINCT c.CategoryID) = 2[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-26 : 13:16:40

SELECT p.*
FROM Product p
INNER JOIN
(
SELECT pc.ProductID
FROM ProductCategories pc
ON pc.ProductID=p.ProductID
INNER JOIN Category c
ON c.CategoryID=pc.CategoryID
AND c.CategoryName IN ('Stationary', 'Office')
GROUP BY pc.ProductID
HAVING COUNT(c.CategoryID)=2)t
ON t.ProductID=p.ProductID

MDCas
Starting Member

5 Posts

Posted - 2008-03-26 : 13:22:05

Thanks guys, tried the first response and it worked like a charm :) I'm amazed about the response speed on this forum as well!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources