Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    59
    Thanks
    5
    Thanked 6 Times in 6 Posts

    Multiple Left Joins Causing Problems

    I am building a product management system which stores sales data about the products and also allows the user to order products from the supplier.

    I have the following tables (I will only include the relevant columns):

    Products
    ID

    Modifiers - These are versions of products eg. red, blue
    ID, Product_ID

    Purchase_Order_Items - These are the items ordered on the supplier
    ID, Modifier_ID, Quantity

    Purchase_Order_Delivery_Items - These are the items which have been received from the supplier
    Purchase_Order_Item_ID, Quantity_Received

    Orders - Orders placed by customers
    ID, Timestamp

    Ordered_Items - Modifiers which have been ordered by customers
    Order_ID, Modifier_ID, Quantity

    Each product can have multiple modifiers and I am trying to get the following information about each modifier by product:

    The ID, The amount we have sold in the last three months, and how many we currently have on order with the supplier.

    I have the following query:
    Code:
    SELECT m.`ID`, SUM(oi.`Quantity`) AS `Sold`, SUM(poi.`Quantity`) - IFNULL(SUM(podi.`Quantity_Received`), 0) AS On_Order
    FROM `Modifiers` AS m
    JOIN `Products` AS p ON p.ID = m.Product_ID
    LEFT JOIN (
       `Purchase_Order_Items` AS poi
       LEFT JOIN `Purchase_Order_Delivery_Items` AS podi ON podi.`Purchase_Order_Item_ID` = poi.`ID`
    ) ON poi.`Modifier_ID` = m.`ID`
    LEFT JOIN (
       `Ordered_Items` AS oi
       JOIN `Orders` AS o ON o.`ID` = oi.`Order_ID` && o.`Timestamp` > 1312970798
    ) ON oi.`Modifier_ID` = m.`ID`
    WHERE p.ID = 252
    GROUP BY m.`ID`
    The reason I have come to that, i.e. why I have put the left joins where I have, is that for each modifier there may not have been any ordered on the supplier but if there have been we may not have received any from the supplier i.e. we're expecting them in.
    The next left join section is because we may not have sold any but if we have then I only want to know how many have been sold in the last 3 months.

    If I remove either one of the left join sections and the appropriate select then the other works fine but when they are both there the number on order and the amount sold come out wrong.

    Any help would be appreciated.

    Thanks

    Mark

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Yes, that makes sense.

    To find the cause of this, just change your SELECT temporarily to *NOT* get SUMs but instead show all the records you are getting.

    E.g.
    Code:
    SELECT m.ID, p.ID, poi.ID, podi.Purchase_Order_Item_ID, oi.ID, oi.Order_ID,
           oi.Quantity, poi.Quantity, podi.Quantity_Received
    Get rid of the GROUP BY and run that.

    Now do you see it?
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    59
    Thanks
    5
    Thanked 6 Times in 6 Posts
    Hi Old Pedant,

    Are you saying what I've done makes sense? or that it makes sense that I don't get the results I expected?

    I've done what you suggested and can see the cause - if I have more than one purchase order it repeats all the sales for each one and vice versa so the sum is obviously giving incorrect results.

    Here are the results:
    Code:
    mID | pID | poiID | DelItemID  |oiID   | Order_ID | oiQty | poiQty | Received
    309 | 252 | 141   | NULL       |9036   | 4299 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |9095   | 4325 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |9466   | 4502 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |10357  | 4939 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |10790  | 5132 	  | 3      | 6        | NULL
    309 | 252 | 141   | NULL       |10810  | 5142 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |11277  | 5375 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |11663  | 5582 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |11683  | 5597 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |11841  | 5666 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |12181  | 5821 	  | 1      | 6        | NULL
    309 | 252 | 141   | NULL       |12712  | 6058 	  | 3      | 6        | NULL
    310 | 252 | NULL  | NULL       |9270   | 4397 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |9611   | 4577 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |9989   | 4762 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |10900  | 5194 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |11562  | 5527 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |11936  | 5712 	  | 1      | NULL     | NULL
    310 | 252 | NULL  | NULL       |12312  | 5875 	  | 1      | NULL     | NULL
    311 | 252 | 47    | 47         |8720   | 4143 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |9952   | 4742 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |10640  | 5082 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |12210  | 5833 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |12517  | 5958 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |12599  | 5997 	  | 1      | 2        | 2
    311 | 252 | 47    | 47         |12793  | 6097 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |8720   | 4143 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |9952   | 4742 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |10640  | 5082 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |12210  | 5833 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |12517  | 5958 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |12599  | 5997 	  | 1      | 2        | 2
    311 | 252 | 107   | 107        |12793  | 6097 	  | 1      | 2        | 2
    311 | 252 | 142   | NULL       |8720   | 4143 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |9952   | 4742 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |10640  | 5082 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |12210  | 5833 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |12517  | 5958 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |12599  | 5997 	  | 1      | 2        | NULL
    311 | 252 | 142   | NULL       |12793  | 6097 	  | 1      | 2        | NULL
    Is there a way to get around this?

    Thanks for your help....again!!

    Mark

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    > [are you saying] that it makes sense that I don't get the results I expected?

    Yes, that's what I was saying. And now, as you say, you can see how it happened.

    To fix it, just include the purchase order number (or id, or something that identifies the individual purchase orders--the field you listed as "poiID" in that output would work) and then also add that to your GROUP BY clause.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    59
    Thanks
    5
    Thanked 6 Times in 6 Posts
    Should I be putting the SUMs back in? Because if I do then I still end up with the same erroneous results, only this time there are five result rows instead of three because of the second GROUP BY clause.
    Code:
    SELECT m.`ID` , poi.ID, SUM( oi.`Quantity` ) AS `Sold` , SUM( poi.`Quantity` ) - IFNULL( SUM( podi.`Quantity_Received` ) , 0 ) AS On_Order
    FROM `Modifiers` AS m
    JOIN `Products` AS p ON p.ID = m.Product_ID
    LEFT JOIN (
    `Purchase_Order_Items` AS poi
    LEFT JOIN `Purchase_Order_Delivery_Items` AS podi ON podi.`Purchase_Order_Item_ID` = poi.`ID`
    ) ON poi.`Modifier_ID` = m.`ID`
    LEFT JOIN (
    `Ordered_Items` AS oi
    JOIN `Orders` AS o ON o.`ID` = oi.`Order_ID` && o.`Timestamp` >1312970798
    ) ON oi.`Modifier_ID` = m.`ID`
    WHERE p.ID =252
    GROUP BY m.`ID` , poi.`ID`
    Gives the results
    Code:
    ID  |  ID  | Sold | On_Order
    309 | 141  | 16   | 78
    310 | NULL | 7    | NULL
    311 | 47   | 7    | 0
    311 | 107  | 7    | 0
    311 | 142  | 7    | 14
    Am I doing something very wrong here and not understanding what you meant or is what I ws hoping for not possible?
    The result I was hoping to get was:
    Code:
    ID  | Sold | On_Order
    309 | 16   | 6
    310 | 7    | NULL
    311 | 7    | 2
    ie there are 3 modifiers on product 252 so I only want three results detailing the amount sold and the amount currently on order with the supplier.

    Mark

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Ugh...yes, I see the problem.

    I didn't tumble to the fact that you have those multiple Purchase_Order_Delivery_Items per purchase order.

    You may have no choice but to do this via sub-queries.

    Get the one sum in one subquery and the other in another.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    May 2009
    Location
    Birmingham, England
    Posts
    59
    Thanks
    5
    Thanked 6 Times in 6 Posts
    Yep that's worked! Thanks...don't know why I didn't think of doing that.

    Although do I need to do both in subqueries? I moved one of them to a sub query and I got exactly what I wanted.

    Thanks again

    Mark.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,574
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    You should only need one query per sum, at worst, so if the main query gets one and the subquery gets the other, you should be okay.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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