Isn't it simply...
AND (
(inventory > 0)
OR (backorder = 1)
)
Regards,
Matthew Walker
/*
Easier, smarter forms:
http://www.matthewwalker.net.nz/inform2
*/
> -----Original Message-----
> From: Paul Giesenhagen [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, 21 May 2002 3:38 p.m.
> To: CF-Talk
> Subject: OT: SQL Query Depending on value
>
>
> 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
