I have considered this. There may be empty locations referenced in the database with 0 stock values for old sold out products so the query would need modifying a little to more like...
SELECT l.locationName FROM locations l LEFT JOIN stock s ON (l.locationName = s.location) GROUP BY locationName HAVING if(isnull(sum(s.stock)),0,sum(s.stock)) = 0 I really wanted to avoid having a table full of locations though. This is still the lesser of two evils though compared to 3 nested loops! > 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:231936 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
