Help with nested query

[SoonerToucan]

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 A & B.

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

This returned zero records though it should have returned the product that is in categorys A&B.

I would appreciate any help available.

Thank you,
-Patrick

 

[PHV]

Have you tried this ?
SELECT * FROM Product
WHERE (ProductID IN (SELECT ProductID FROM ProductCategories WHERE CategoryID = 'A'))
AND
(ProductID IN (SELECT ProductID FROM ProductCategories WHERE CategoryID = 'B'))



Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SoonerToucan]

Acutally the CategoryIDs are numbers so I didnt use them, I just posted them as A & B for ease of viewing(my mistake). I just tossed the tick marks on for kicks and it didnt work. So I am still stuck, but thank you for the quick response!

 

[PHV]

Have you carefully read my previous post ?
SELECT * FROM Product
WHERE (ProductID IN (SELECT [blue]ProductID[/blue] FROM ProductCategories WHERE CategoryID = A))
AND
(ProductID IN (SELECT [blue]ProductID[/blue] FROM ProductCategories WHERE CategoryID = B))


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SoonerToucan]

PHV you rock, I selectivly read your reply. Upon re-reading your post and applying the changes it works great. Once again thank you very much for your help!

Gratefully,
Patrick

 

[r937]

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

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

this method is easily extended to additional categories, whereas the multiple subqueries is more cumbersome (and probably less efficient)

rudy

http://r937.com/

SQL Consulting