My suggestion when mixing AND & OR is to wrap everything in parentheses.
This way there can be no confusion.
SELECT left(subject,20) as newsubject, * FROM advert_details
WHERE
(
town = '#form.towny#'
OR
(
natpaid = Yes
AND nationwide = Yes
)
)
<cfif isdefined('form.category')>
AND category = '#form.category#'
</cfif>
Is that the correct grouping?
Steve
-------------------------------------
Steven Monaghan
Oracle DBA / Cold Fusion Developer
MSC Industrial Direct Co., Inc.
Melville, NY
[EMAIL PROTECTED]
http://www.mscdirect.com
-------------------------------------
-----Original Message-----
From: W Luke [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 24, 2001 1:28 PM
To: CF-Talk
Subject: AND & OR placement in SQL
Hi,
Bit confused on the placement of AND & OR operators in the same query.
<cfquery cachedwithin="#createtimespan(0,0,10,0)#" name="getads"
datasource="#DSN#">
SELECT left(subject,20) as newsubject, * FROM advert_details
WHERE town = '#form.towny#'
OR (natpaid = Yes AND nationwide = Yes)
<cfif isdefined('form.category')>
AND category = '#form.category#'
</cfif>
</cfquery>
Before form.category is defined, the query works as I want it to. But once
form.category is defined, will the addition of an AND operator muck up the
query?
Thanks
Will
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Get the mailserver that powers this list at http://www.coolfusion.com
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
