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

    Unanswered: 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,
    -Patrick
    Last edited by SoonerToucan; 03-02-04 at 20:57.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    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
    (*) > 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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