linking main query to subquery

[jworisek]

Heres my query:

[code]
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
[/code]

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.

Thanks

[wickning1]

Looks good to me (except a missing parenthesis and the fact that you call it shipping_data but it is shipped_data in the query).

Are you having a problem with it or did you just want to double check that it's ok?

[jworisek]

oh, sorry... I have the code on a PC not connected to the web so I typed the query and had a typo there.

When I try to place the line_info_id=N.line_info_id into the sub query I get:

Unknown table 'N' in where caluse

if I do it without a where caluse for the subquery it works fine but pulls 8,601 rows from SD2 and 21,578 rows from shipping_data (every row in the table).

[wickning1]

Ah, I understand. Yes, you often want to duplicate your WHERE condition inside subqueries to pare down the processing.

I'm not able to glean a lot about your database structure, but assuming incoming_data has the order_id column, it would go like this:

[code]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(s.pieces_shipped) as shipped, s.line_info_id
   from shipping_data s
   INNER JOIN incoming_data d ON d.line_info_id=s.line_info_id
   WHERE d.order_id='$order_id'
   GROUP BY s.line_info_id
) as SD2 ON (SD2.line_info_id=N.line_info_id)
where order_id='$order_id' group by order_id[/code]

[jworisek]

So you are basically saying run the same query in the subquery?

I have other instances of a very similiar query where it doesn't get just an order_id value... It might get a customer_id or something else. But basically I can join it together the same basic way for more complex queries?

[wickning1]

Basically you just want to GROUP BY on the smallest number of rows that are necessary. Come up with a subquery that produces exactly the information you need, and no more. In your example, there is no need to sum up shipping data for orders you don't care about.

Often, when you have something in your main WHERE clause, you'll want to place it inside the subquery as well. Not always, just when it makes sense to do it.