JOIN and GROUP BY

[streetlife]

Basically, I am having problems using SUM() with multiple JOINs and GROUP BY. The values returned for the SUM() are the same for each row, which tells me that the values are being added together before the GROUP BY, not after. I think that ideally I would be able to do a GROUP BY for each JOIN. Hopefully someone can tell me how to do this in the one query. Thanks

SELECT purOrderItems.invItemFrame_id, invItemFrames.invItemFrame_name, purOrderItems.purOrderItem_unitCost,
SUM( purOrderItems.purOrderItem_quantity ) AS quantity,
IFNULL( SUM( purOrderReceivedItems.purOrderReceivedItem_quantity ) , 0 ) AS received,
IFNULL(SUM(purOrderCancelledItems.purOrderCancelledItem_quantity),0) AS cancelled,
FROM purOrders, invItemFrames
LEFT JOIN purOrderItems ON purOrders.purOrder_id = purOrderItems.purOrder_id
LEFT JOIN purOrderReceived ON purOrders.purOrder_id = purOrderReceived.purOrder_id
LEFT JOIN purOrderReceivedItems ON purOrderReceived.purOrderReceived_id = purOrderReceivedItems.purOrderReceived_id
LEFT JOIN purOrderCancelled ON purOrders.purOrder_id = purOrderCancelled.purOrder_id
LEFT JOIN purOrderCancelledItems ON purOrderCancelled.purOrderCancelled_id = purOrderCancelledItems.purOrderCancelled_id
WHERE invItemFrames.invItemFrame_id = purOrderItems.invItemFrame_id AND purOrders.purOrder_id = '91'
GROUP BY invItemFrame_id
ORDER BY invItemFrames.invItemFrame_name

[r937]

GROUP
BY purOrderItems.invItemFrame_id
, invItemFrames.invItemFrame_name
, purOrderItems.purOrderItem_unitCost

[streetlife]

GROUP BY purOrderItems.invItemFrame_id, invItemFrames.invItemFrame_name, purOrderItems.purOrderItem_unitCost

invItemFrame_id invItemFrame_name purOrderItem_unitCost

Thanks for your reply, but that doesn't appear to affect anything. Some of the quantity are still set to either 75,150, or 300, all the received values are at 81, and the cancelled are at 0 because there are no cancelled values. Any ideas?

[r937]

yes, it must be because the relationships that you
include in the query involve unrelated rows
(unrelated to each other, even though related to purOrders)

you are getting cross join effects

unlesds you're on 4.1 which supports subqueries,
try breaking the query apart into two or more,
each of which concentrates on its own sums

[streetlife]

Well I just checked with my administartor and he's not gonna upgrade to 4.1 cos it's still in alpha, it was hard enough just to get him to upgrade to 4 a couple of months back!
So should i create some temporary tables with each query and then join them together, or just do the joining in PHP? Or do you have a better suggestion?

Again, thank you so much for your help.

[r937]

since you are only querying one PO,
i would combine the results of the
several queries in my scripting language

[streetlife]

Ok, thanks so much!