Finding Records

[Jellyhead]

I have a table with 7 columns (keyword1, keyword 2, etc) - what I need to do is
to find matching rows based on up to seven input fields, but not less than
three.

Thus, if my user types in seven input fields, I need it to return all records
that match three or more of the input fields. Caveat: input field 3 may return
under column 5, input field 6 may be under column 1, etc, so the input field
can match anyone of the seven columns.
Thus if my user is looking for a series of keywords, it must return all
records that have three or more of these keywords.

I've written SQL queries longer than "war and peace", but its not 100%
effective and very inelegant. Can anyone suggest a more elegant and easy way
to write this query?

Using CFMX7 and MS Access (I know, I know...)

[The ScareCrow]

Redesign your db structure. You should not have those columns in your table.

You should have another table, which would contain an id pointing to the
record it is associated with and a keyword column
eg
Table - myKeyWords
recordId number
myKeyWord -varchar

Then your query will be

Select YourColumn
From yourTable Inner Join myKeyWords On yourTable.ID = myKeyWord.recordid
Where 0=0 <!--- zero equals zero --->
<cfif Len(Trim(form.keyword))>
And myKeyWord.myKeyWord = '#form.keyword#'
</cfif>
<!--- repeat as many times as needed --->
<cfif Len(Trim(form.keyword1))>
And myKeyWord.myKeyWord = '#form.keyword1#'
</cfif>
Having Count(myKeyWord) >= 3

Ken

[Jellyhead]

Thank you, I'm now actually getting somewhere, but I?m still stuck. I see the
logic, but the syntax escapes me. Lets get more specific.

Table 1: cities
rowid - counter
city - varchar

(based on ScareCrow's suggestion)

Contains a list of some 50-odd cities. New York, Johannesburg, Blacktown,
Seattle Wellington, etc

Then
Table 2: clients
rowid - counter
client_id - varchar
city1 - varchar
city2 - varchar
...
city 7 - varchar

This has about 450 unique client records, no client is represented twice.
Now I need to select into 7 input fields 7 cities. The query must return all
client_ids that went to 3 or more of these specified cities.
(Don't worry about the spelling and consistency - city list is a genereated
drop-down box, so we don't need "like" or "trim" functions - I think)

Please talk to me like I'm a 2-year-old, I've been sitting with this all day!
Joins are new to me, and I'm battling a bit.

[The ScareCrow]

I assume that the "client_id" is the unique id to identify the client, correct ?

Then

Table 1: cities
rowid - counter
city - varchar

Table 2: clients
rowid - counter
client_id - varchar

Table3:clients_cities
client_id - varchar
rowid - number

Lets assume that on the page the user selects 7 cities from the dropdown
select list.
This dropdown list will be built like

<select name="select_cities">
<cfoutput query="qry_cities">
<option value="#rowid#">#city#</option>
</cfoutput>
</select>

This will result in a list of rowid's being passed to the action page
containing the query

The query

Select client_id
From clients Inner Join clients_cities On clients.client_id =
clients_cities.client_id
Where 0=0
<cfif Len(Trim(form.select_cities)>
And clients_cities.city IN (#form.select_cities#)
</cfif>
Having Count(client_cities.city) >= 3
Group By client_id

This query will join the 2 tables together only where the client_id is found
in both tables. It will then filter the recordset to only match the records
where the city column in the clients_cities table is in the list. It will then
group the resultset on the client_id. Then filter the recordset to only
include client_id's that have a count of the city column of 3 or more

Ken