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 > ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
