It might help to know which database you are using (SQL Server?). There are many ways to do this, but I don't think you provided enough detail to identify the best method. Below is one method.
SELECT 1 FROM product p WHERE product_id = #id# AND (stock_level > 0 OR EXISTS ( SELECT 1 FROM product_to_product_options ptpo WHERE ptpo.product_id = p.product_id AND ptpo.stock_level > 0 ) ) If the query returns a row, then the product is in stock. Good luck, Mike Chabot On Mon, Mar 31, 2008 at 8:45 PM, Mike Little <[EMAIL PROTECTED]> wrote: > help! i am stuck... > > ok, for my product catalogue i have included a basic inventory system. > > product > ------------- > product_id > track_stock BIT > stock_level INT > reorder_level INT > > product_to_product_options > -------------------------- > product_id > product_options_id > stock_level INT > reorder_level INT > > product_options > --------------------- > product_options_id > product_option_title > > basically, if a product has options (eg. small, medium, large) then the stock > is tracked at the option level. if not then it is tracked at the product > level. > > my big problem is, how do i determine if a product is available for purchase > eg. it has a stock level of zero - either at the product level or for a > particular option? > > i need to develop a system for displaying products on a summary page. > > any help would truly be appreciated. > > mike > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;192386516;25150098;k Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:302389 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
