Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Posts
    6

    Question Unanswered: Help on my SQL Query for school

    Hey Guys,

    I am pretty new to this whole SQL query thing. I'm taking a DB Dev. Class and am starting to enjoy it. Could someone help me with this query? Here is the table structure and the question.

    http://i43.tinypic.com/2h7hv0j.png

    4. Which products have NOT been ordered?

    I've tried this:

    SELECT OrderID
    From [Order Details]
    Where ProductID NOT IN
    (Select ProductID
    From Products)

    (Nothing showed up) - Just the OrderID column, but it was blank.

    Also, I looked in the database and found that every ProductID has an OrderID. So does this mean every product has been ordered. We did this in class and I know there is a tangible right answer. Any help?
    Last edited by Napster; 01-27-09 at 10:18.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - let's translate your query into natural English:

    "Show me all the ID's for Order Details
    Where the product ordered is not in the Products table"

    Do you see what you've done wrong now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Posts
    6
    Yes, I know my query is wrong.

    So what I should want is

    "Show me all the ProductID's
    in the Order Details Table
    That do not have a OrderID"

    How do I make that into SQL?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your natural English specification is not right - let's get that right then the SQL writes itself (you have the structure correct already, you've just jumbled the names up).

    "Show me all the ProductID's
    in the Order Details Table
    That do not have a OrderID"
    this does not make sense - if a Product ID is in the Order Details table then it MUST have an OrderID.
    Line one is correct. Line two is wrong. Line three is wrong.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2009
    Posts
    6
    Wait, how do you know if a ProductID is in the Order Details Table it must have an OrderID? OHHHHHHHH That makes sense!!!! (Light Bulb) Because its the details about the order that was already made. Gotcha. Ok So then it should look like this:

    "Show me all the OrderIDs
    in the Orders Table
    that are not in the OrderDetails Table"

    Correct?

  6. #6
    Join Date
    Jan 2009
    Posts
    6
    SELECT ProductID
    FROM Products
    WHERE ProductID NOT IN
    (SELECT ProductID
    FROM [Order Details])

    GOT IT!! REVELATION!!

  7. #7
    Join Date
    Jan 2009
    Posts
    6
    Answer Was 9 (The ProductID that is)

    http://i44.tinypic.com/2zea328.png

    *Triple Post'

    You make it seem so easy, you have no idea how frustrated I was over that!

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Napster
    *Triple Post
    Block

    Good job - your natural English description still wasn't quite there but good query
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jan 2009
    Posts
    6
    Thank you so much!

Posting Permissions

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