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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
