On Tue, Dec 2, 2008 at 11:06 AM, MB Software Solutions General Account <[EMAIL PROTECTED]> wrote: > Background: > * MySQL5 - MyISAM tables > * Customer is a pallet creation company--they manufacture pallets and > then treat the pallet (heat/dip/other) before shipping to customer. > * 3 tables in focus for this: > -- ORDERS: actual pallet orders > -- QUEUE: orders sent to the treatment area -- this is a MANY child > for the ORDERS table > -- QTY_IN_QUEUE: it's basically the SUM on the QUEUE.QTYs for each > order so that I don't have to always do a SUM of the QUEUE table to know > the total quantity sent to the treatment area. This allows me to a have > a simple 1:1 INNER JOIN query that quickly returns the sum qty from the > ORDERS table -----------------------------------------------------------
You only need 2 tables for data and one for a lookup. Select Sum(q.*), q.TypeID, pt.Description from Orders Ord left join QUEUE q on ord.OrderNumber = q.OrderNumber left join ProcessTypes pt on pt.ID = q.PTID where ord.shippedDate is Null group by q.TypeID, pt.Description You may have a flag set in Queue that defines its been processed so use that instead and drop the entire use of Orders table. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
