Jump to content

linking main query to subquery


jworisek

Recommended Posts

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
Link to comment
Share on other sites

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).
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.