...

View Full Version : Multiple Left Joins Causing Problems



markspark100
11-15-2011, 04:20 PM
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:


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

Old Pedant
11-15-2011, 10:33 PM
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.


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?

markspark100
11-16-2011, 10:16 AM
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:


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

Old Pedant
11-16-2011, 07:57 PM
> [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.

markspark100
11-17-2011, 01:51 PM
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.


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


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:


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

Old Pedant
11-17-2011, 09:00 PM
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.

markspark100
11-18-2011, 02:56 PM
Yep that's worked! Thanks...don't know why I didn't think of doing that.:confused:

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.

Old Pedant
11-18-2011, 08:49 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum