Querying multiple fields

[Nat5]

I want to create a query where data in one form field matches any of six other
fields the record is selected. Anyone have an example of how to write this?
Seems simple enough but nothing I'm doing works. I'm querying SQL.

cfif form.data EQ database.field1 or database.field2 or etc

[jorgepino]

can you post some code.

[paross1]

I don't see a WHERE clause in your SELECT. Also, since you are joining two
tables, you are going to get a Cartesian join (number of rows equaling the
product of both tables) unless you relate the two tables to one another, either
in a JOIN or WHERE clause.

Phil

[jorgepino]

where is the WHERE?

[Nat5]

Thanks. I redid the some of it and left that out by accident.

I'm really trying to figure out this part:
<cfif isDefined("Form.certarea")>
AND appCert.cert1Area EQ '#form.certarea#'
</cfif>

I need form.certarea to not only match up with appCert.cert1 but also cert2,
cert3, cert4, cert5 and cert6 fields. I tried to use an OR but couldn't get it
to work. Can someone tell me if I should use OR - or is this even the right
way to search through more than one field? I have Ben Forta's web application
construction book but can't find an example.

[paross1]

First off, you wouldn't use EQ when comparing your database field against a
value, but would use = instead.

Perhaps, something like this:

<cfif isDefined("Form.ID") AND '#Form.ID#' is not "">
AND applicantTracking.ID = '#form.ID#'
</cfif>
<cfif isDefined("Form.certarea")>
AND (appCert.cert1Area = '#form.certarea#'
OR appCert.cert2Area = '#form.certarea#'
OR appCert.cert3Area = '#form.certarea#'
OR appCert.cert4Area = '#form.certarea#'
OR appCert.cert5Area = '#form.certarea#'
OR appCert.cert6Area = '#form.certarea#')
</cfif>

Phil

[Nat5]

This works! Thanks!:)