#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0

    SQL to find how many products have not been ordered?


    I have a product table and an orders table which are joined by productID.

    What SQL code can I use to list the items in my `products` table that do not appear in my `orders` table?

    Thanks!
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Euskadi
    What SQL code can I use to list the items in my `products` table that do not appear in my `orders` table?
    oh, what a lame homework assignment

    but what you're asking should have been covered in class

    what have you tried so far?

    also, is this a written assignment or are you supposed to actually run this query?

    if the latter, please do a SHOW CREATE TABLE for each table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    I've been trying to find what sort of function to use, I thought it might be something like isnull() but apparently not. Logically I am thinking I have to count the number of product.productID instances that do not have a matching instance in orders.productID
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    no, you're looking at the wrong part of the problem

    if you're supposed to return all the products that aren't on any order, then counting them doesn't produce the desired results, and you will lose marks

    besides, the right part of the problem is not whether you list the products or count them, it's figuring out which ones aren't on any order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    Please, if you are posting homework questions in a forum (this one or any others)

    a) indicate it is homework, you will get a better response if you do

    b) explain what part of the problem you are having trouble understanding

    c) show us examples of how you have attempted to solve the problem and people will be more than happy to assist you along the way.

    Without doing that you (or other posters doing similar) are asking others to do the work for you, or you are not realizing that is what you are asking but essentially are asking.

    We don't mind helping folks out who are new to databases (nor others in other forums with their topics), just want to make sure you wish to and are trying to do the work.

    Thanks
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    I appreciate and fully understand your reluctance to flat out give me the answer for a homework assignment. This is not my entire homework (probably why r937 called it lame!) it is one of about 20 SQL queries I must come up with, there are others which are similar to this one so I was hoping for an answer to this to help me understand the others.
  12. #7
  13. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,144
    Rep Power
    1316
    okay so would you like to show us what you have attempted so far to solve this one? we can help you understand the logic of the solution as well as to point out what you have done incorrectly.

    also think about what r937 has already told you.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    I actually have to just output the number of products not ordered, and I think I have it with:

    Code:
    SELECT count (*) AS notordered
    FROM product
    WHERE product.prodID NOT IN
        (SELECT orders.prodID FROM orders WHERE orders.quantity > 1)
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2006
    Posts
    2,436
    Rep Power
    1688
    Without wishing to rain on your parade too much - what happens when the order quantity is 1?
    The moon on the one hand, the dawn on the other:
    The moon is my sister, the dawn is my brother.
    The moon on my left and the dawn on my right.
    My brother, good morning: my sister, good night.
    -- Hilaire Belloc
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    I change it to > 0

    not sure why I put > 1
  20. #11
  21. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Euskadi
    I change it to > 0

    not sure why I put > 1
    i'm not sure why you had to put > 0

    you actually have orders with quantity = 0 ???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  22. #12
  23. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    Originally Posted by r937
    i'm not sure why you had to put > 0

    you actually have orders with quantity = 0 ???
    No, it was giving me an error when I didn't have a WHERE clause, but it must have been for a different reason.

    So as not to make a new thread, I've encountered another problem with a different question and would appreciate a bit of guidance (not the answer :P)

    I have a `book` table which has the fields isbn_no, authorID, publisher, rrp and I need to create a query that finds if any authors have the same publisher - and if so I must output the name of the author and publisher. I am thinking along the lines of using "as" to make 2 book tables and compare table1.publisher with table2.publisher but I'm not too sure how to go about it, or if that is the best way?
  24. #13
  25. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2011
    Posts
    9
    Rep Power
    0
    Anyone?
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,927
    Rep Power
    378
    I have a table of recipes, a table of ingredients, and a linking table.

    I want to list all recipes which share ingredients with at least one other recipe, and show which ingredients they share...
    Code:
    SELECT * FROM recipe;
    +-----------+-------------------------+
    | recipe_id | recipe                  |
    +-----------+-------------------------+
    |         1 | Macaroni & Cheese       |
    |         2 | Cheese on Toast         |
    |         3 | Beans on Toast          |
    |         4 | Cheese & Beans on Toast |
    |         5 | Toast & Jam             |
    +-----------+-------------------------+
    
    SELECT * FROM ingredient;
    +---------------+------------+
    | ingredient_id | ingredient |
    +---------------+------------+
    |             1 | Macaroni   |
    |             2 | Cheese     |
    |             3 | Beans      |
    |             4 | Toast      |
    |             5 | Jam        |
    |             6 | Spaghetti  |
    +---------------+------------+
    
    SELECT * FROM recipe_ingredient;
    +-----------+---------------+
    | recipe_id | ingredient_id |
    +-----------+---------------+
    |         1 |             1 |
    |         1 |             2 |
    |         2 |             2 |
    |         2 |             4 |
    |         3 |             2 |
    |         3 |             3 |
    |         3 |             4 |
    |         4 |             2 |
    |         4 |             3 |
    |         4 |             4 |
    +-----------+---------------+
    
    SELECT DISTINCT r.recipe
         , i.ingredient 
      FROM recipe_ingredient ri1 
      JOIN recipe_ingredient ri2 
        ON ri2.recipe_id <> ri1.recipe_id 
       AND ri2.ingredient_id = ri1.ingredient_id 
      JOIN recipe r 
        ON r.recipe_id = ri1.recipe_id 
      JOIN ingredient i 
        ON i.ingredient_id = ri1.ingredient_id;
    +-------------------------+------------+
    | recipe                  | ingredient |
    +-------------------------+------------+
    | Cheese on Toast         | Cheese     |
    | Beans on Toast          | Cheese     |
    | Cheese & Beans on Toast | Cheese     |
    | Macaroni & Cheese       | Cheese     |
    | Beans on Toast          | Toast      |
    | Cheese & Beans on Toast | Toast      |
    | Cheese & Beans on Toast | Beans      |
    | Cheese on Toast         | Toast      |
    | Beans on Toast          | Beans      |
    +-------------------------+------------+
    Last edited by cafelatte; November 9th, 2011 at 12:47 PM.

IMN logo majestic logo threadwatch logo seochat tools logo