Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Question Unanswered: A little SQL help required

    Hi guys,

    Just a quick one, as I can't seem to get this quite right.

    I have two tables:
    1. leather_ref_products - Contains information about the various products.
    2. leather_main - Contains information about items in stock.


    I have been asked to produce a list that shows:
    • leather_ref_products/leather_main.[Product Code] - This is the relationship field.
    • leather_ref_products.[Product Description]
    • COUNT(*) WHERE leather_main.[Company] = 'STOCK'


    The catch is that the list needs to contain all the product codes for a certain range (leather_ref_products.[Range] = 'JT'), including any that are out of STOCK. This should therefore just read 0 in the COUNT() column.


    I thought it would just be a case of using a left join, but if I do:

    Code:
    SELECT leather_main.[Product Code], leather_ref_products.[Product Description], COUNT(*) AS [Quantity]
    FROM leather_ref_products LEFT JOIN leather_main
      ON leather_main.[Product Code] = leather_ref_products.[Product Code] 
    WHERE leather_ref_products.[Range] = 'JT' 
    AND leather_main.[Company] = 'STOCK'
    GROUP BY leather_main.[Product Code], leather_ref_products.[Product Description] 
    ORDER BY leather_main.[Product Code], leather_ref_products.[Product Description];
    It still only returns the values that are actually in STOCK.

    I'm assuming I need to move that part of the WHERE statement somewhere else, but I'm not quite sure where?

    If anyone can shed some light on this situation, I'd be most grateful.


    Thanks!
    Looking for the perfect beer...

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    At first glance (w no data on my end) I would reverse the outer join to the [leather_main] table. You want ALL items in the [leather_ref_products] tbl.

    And I would count a specific field (this may not matter tho) Count([Product Code]).

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by ranman256 View Post
    At first glance (w no data on my end) I would reverse the outer join to the [leather_main] table. You want ALL items in the [leather_ref_products] tbl.

    And I would count a specific field (this may not matter tho) Count([Product Code]).
    It doesn't seem to work.

    Again, I'm sure that the issue is the placement of the WHERE leather_main.[Company] = 'STOCK' statement. An outer join will return everything from the leather_ref_products table, but that's only if the ON statement doesn't return it. A WHERE will still override the outer join.

    At least that's my understanding of it anyway.


    Any other ideas?
    Looking for the perfect beer...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You explained that:
    The catch is that the list needs to contain all the product codes for a certain range (leather_ref_products.[Range] = 'JT'), including any that are out of STOCK.
    However you use:
    Code:
    AND leather_main.[Company] = 'STOCK'
    Have a nice day!

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by Sinndho View Post
    However you use:
    Code:
    AND leather_main.[Company] = 'STOCK'
    Yeah, because I need to count the number that are in STOCK.

    Basically, my colleague is looking to make an order to replenish our STOCK levels for the JT range.

    So what she wants is a list of every product in that range, along with how many (if there are any) in STOCK.

    Any that aren't in STOCK, should just have 0 entered for the COUNT() quantity.


    EDIT: From what I can see, I need to move the Company = 'STOCK' bit into the ON clause of the join... However, this doesn't seem to work in Accesses SQL. Works fine in mySQL, but not in Access... So I'm still a little stumped.
    Last edited by kez1304; 05-27-14 at 13:19.
    Looking for the perfect beer...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you can have multiple rows as stock for a single product then with the greatest respect your design is FUGLY.



    SELECT leather_main.[Product Code], leather_ref_products.[Product Description], leather_main.Company, Quantity
    FROM leather_ref_products LEFT JOIN leather_main
    ON leather_main.[Product Code] = leather_ref_products.[Product Code]
    WHERE leather_ref_products.[Range] = 'JT'
    GROUP BY leather_main.[Product Code], leather_ref_products.[Product Description]
    ORDER BY leather_main.[Product Code], leather_ref_products.[Product Description];
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What about a subquery:
    Code:
    SELECT a.[Product Code], leather_ref_products.[Product Description], COUNT(*) AS [Quantity]
      FROM leather_ref_products LEFT JOIN 
          (SELECT leather_main.[Product Code]
             FROM leather_main
            WHERE leather_main.[Company] = 'STOCK'
          ) AS a 
        ON leather_ref_products.[Product Code]  = a.[Product Code] 
     WHERE leather_ref_products.[Range] = 'JT'
    GROUP BY a.[Product Code], leather_ref_products.[Product Description] 
    ORDER BY a.[Product Code], leather_ref_products.[Product Description];
    Have a nice day!

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    if you can have multiple rows as stock for a single product then with the greatest respect your design is FUGLY.
    I appreciate it might seem that way, but really the leather_main table holds all kinds of information on it. Including a unique serial number that's assigned to each individual product (there are about 60,000 of these), so it has to have multiple rows. No other way to do it unfortunately. Thus why there's the join for the description, range, etc., from the product code.


    Sinndho, you're code is nearly spot on... There's one issue though. If I use the a.[Product Code], it doesn't pull in anything if it's not in STOCK.

    It also uses '1' for the quantity of everything that doesn't exist, so if I change it to read "leather_ref_products.[Product Code]", it'll pull in all the data (with the Product Codes for every product), but then because it's using '1' for the quantity it's impossible to tell what is and isn't in STOCK.

    Any ideas on how to get '0' to show (I'm guessing you can use an IIF, but that seems a little bit messy) for those not in STOCK?

    [I realise I could use:
    Code:
    SELECT a.[Product Code], leather_ref_products.[Product Code], leather_ref_products.[Product Description], COUNT(*) AS [Quantity]
    To pick out which is which, but it's not really ideal].

    So thanks a lot, I can get the information I need out of it, I'm just always wanting to learn, and figure there must be a way to get this query perfect.

    Last edited by kez1304; 05-27-14 at 16:11.
    Looking for the perfect beer...

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT a.[Product Code], 
           leather_ref_products.[Product Description], 
           COUNT(a.Company) AS Quantity
      FROM leather_ref_products LEFT JOIN (SELECT leather_main.[Product Code],leather_main.Company
                                             FROM leather_main
                                            WHERE leather_main.[Company] = 'STOCK'
                                          ) AS a 
        ON leather_ref_products.[Product Code]  = a.[Product Code]
     WHERE leather_ref_products.[Range] = 'JT'
    GROUP BY a.[Product Code], leather_ref_products.[Product Description]
    ORDER BY a.[Product Code], leather_ref_products.[Product Description];
    Have a nice day!

Posting Permissions

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