SELECT sum(received) items_received, order_id, sum(received)-IF(ISNULL(sum(shipped)),0,sum(shipped)) as items_shipped from line_info N INNER JOIN incoming_data ID ON (ID.line_info_id=N.line_info_id) LEFT JOIN (SELECT sum(pieces_shipped) as shipped, line_info_id from shipped_data group by line_info_id) as SD2 ON (SD2.line_info_id=N.line_info_id) where order_id='$order_id' group by order_id
Right now it sums the entire shipping_data table with the subquery before using just the one that links the tables. Is there a way to pass to the subquery where the main query is at (with respect to the N.line_info_id)? Or is there a better way to structure this query so that it doesn't do unnecessary work?
There will always be a record in the incoming_data table, but there may be no rows or multiple rows (due to multiple shipments) for each item in the shipping_data table.