Stephen Russell wrote: > 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. >
You don't understand...I know I could do the SUM on the LEFT JOIN query---I don't want to do that because this query is being fired for SEVERAL orders at a time. Picture the schedule grid as this: there are 4 nailing machines on the lefthand (vertical) axis, and 5 dates across the top, with each nailer/date coordinate holding up to 4 orders. Here's a screenshot: http://mbsoftwaresolutions.com/images/scheduler.png Each of those "cells" needs to know the quantity sent to the treatment queue. If the total quantity has been sent, then the border around the cell changes to a gray or yellow, depending on whether it's done being treated or not. At that point, the production supervisor can take these "virtual magnets" off the "board" by right-clicking the "cell." It's a much easier management process than the old manual ways they used to do with a big physical magnet board where they'd use erasable marker to list details of each order. I don't want to tap that query for a LEFT JOIN using the aggrevate SUM function. I'd rather just hit the QTY_IN_QUEUE table for a single 1:1 match. I did that for performance, as the process before wasn't as quick as I'd like it to be. Think of a SEEK instead of processing a SUM SQL. ;-) _______________________________________________ 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.
