In the database create a table for Isle, Bay, Shelf, and Product.
Isle_ID
Isle_Status
Bay_ID
Isle_ID
Bay_Status
Shelf_ID
Bay_ID
Shelf_Status
Product_ID
Shelf_ID
Populate those tables with rows for every possible Isle,Bay, and shelf
in the warehouse. Then you can do a single query:
SELECT Isle_ID, Bay_ID,Product_ID
FROM Isle I,Bay B,Shelf S
WHERE 0=0
AND I.Isle_ID=B.Isle_ID
AND S.Bay_ID=B.Bay_ID
AND Shelf_ID NOT IN (
SELECT Distinct Shelf_ID
FROM Product
)
-Brandon
-----Original Message-----
From: James Smith [mailto:[EMAIL PROTECTED]
Sent: Friday, February 10, 2006 8:04 AM
To: CF-Talk
Subject: Finding empty locations
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:231929
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
