Yes, that is where the problem arises... Basically if inventory is > 0 show the item... no problems, but if the inventory level is <= 0 I need to see if backordering is allowed and if it is, show the item, if it isn't do not show the item.
Make sense? Paul Giesenhagen QuillDesign > First thing I see, Paul is that you say > "if inventory is 0 or below, then I need to check backorder value" > > But your SQL says: > ... and inventory > 0 ... > > ??? > > Aren't these opposites? > > > On 5/20/02 8:38 PM, "Paul Giesenhagen" <[EMAIL PROTECTED]> wrote: > > > I am writing a join query that displays information depending on a value, > > > > There are two columns that deal with inventory, they are > > inventory = number in stock (could be a negative) > > backorder = either a 1/0 do we allow backordering or not, if it is 1 Yes, we > > allow backordering if 0 No we do not. > > > > The situation is, if inventory is 0 or below, then I need to check backorder > > value to see if it is a 1 or zero. If it is a 1, then I want to display that > > value, if it is a 0 and the inventory is 0 or below then do not show the > > value. > > > > Here is what I have so far! > > > > <cfquery datasource="#caller.prefs.dsn#" name="getoption"> > > select o.type, v.id AS itemid, v.item, v.price > > from #caller.prefs.option_table# o, #caller.prefs.option_values_table# v > > where o.id = '#options.optionid#' > > and o.id = v.optionid > > and inventory > 0 > > and backorder = 1 > > order by v.sort, o.name > > </cfquery> > > > > This query will not run correctly .. if inventory is > 0 great, then we don't > > need to know about the backorder flag... but if inventory is 0 or less, then > > we want to see if the backorder flag is 1 or 0 .. if it is 1, then I want the > > value to show, but if it is 0, do not show the value. > > > > I know I could do this in CF with <cfif's> but would rather have it done in > > the query. > > > > The ending result needs to show the itemid, item, and price > > > > Any suggestions? > > > > TIA > > > > Paul Giesenhagen > > QuillDesign > > > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
