James, Sometimes you can make use of a reference table to do something like this...you can populate it with your nested loop initially (you should only need to change this table if something about your warehouse layout changes), but then just do the query as a join... <cfquery> SELECT a.* FROM StockLocations a LEFT JOIN Stock b ON a.location = b.location WHERE b.location IS NULL </cfquery> -jim
On 2/10/06, James Smith <[EMAIL PROTECTED]> wrote: > Our warehouse has stock arranged in a way that makes it very easy to find. > If I ask someone to get the title from 03.28.07 they just have to go to isle > 3 then walk down it to bay 28 and get the product from shelf 7. Simple. > > I now need to write a query that can tell me where in the warehouse there is > an empty location and this is more complex than it should be. The only way > I can think is to have three nested loops (one for each of isle, bay and > shelf) to generate a location and then query the database to see if there is > any stock in it. The problem is that this could easily lead to thousands of > queries being run and that is going to cause performance problems. > > IE: > > <cfloop from=1 to=#isles# index=x> > <cfloop from=1 to=#bays# index=y> > <cfloop from=1 to=#shelves# index=z> > <cfset location = "#x#.#y#.#z#"> > <cfquery> > SELECT * FROM Stock WHERE location = #location# > </cfquery> > </cfloop> > </cfloop> > </cfloop> > > Can anyone think of a better way? > > -- > Jay > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231928 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
