Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004

    Unanswered: Help With Nested Query

    I am having trouble with the following query.

    Important Tables:
    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.

    Attempted Solution:
    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 would appreciate any help available.

    Thank you,
    Last edited by SoonerToucan; 03-02-04 at 20:57.

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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
    PHP Code:
    select ProductName
      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
        by ProductName 
    having count
    (*) > | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 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.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts