First of all I'd like to say that it's almost never a good idea to use
comma-delimited lists in a database. It violates the whole DB concept.
You should have another table that is used solely to relate record(s) of
type A to record(s) of type B.
That being said, here is the solution to your original question:
1. Jerry is correct about placing commas before and after the list in the
DB - it is a must.
2. CONTAINS is a CF function, not SQL. You must use LIKE.
+-----------------------------------------------+
Bryan Love
Macromedia Certified Professional
Internet Application Developer
Database Analyst
TeleCommunication Systems
[EMAIL PROTECTED]
+-----------------------------------------------+
"...'If there must be trouble, let it be in my day, that my child may have
peace'..."
- Thomas Paine, The American Crisis
"Let's Roll"
- Todd Beamer, Flight 93
-----Original Message-----
From: Angel Stewart [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 12:01 PM
To: CF-Talk
Subject: SQL: Comparing elements in a list to comma delimited data in a
table field
Hello all,
I want to take a comma delimited list created in a Search form, and
check to see if any of the elements in that list is contained in a field
in the database which is also comma delimited in a compound CFQUERY
statement.
SO...form.coatings = "1,5,10,13"
The field coatings in the database contains "1,3,14".
So therefore that record will be a part of the resultset.
The next record coatings field may contain "7,8,9" and therefore should
NOT be a part of the resultset.
The SQL I tried was:
<CFQUERY name="sresults" datasource="napca">
SELECT * FROM tblMembers
WHERE 0=0
<CFIF IsDefined("membertype") AND #membertype# NEQ 0>
AND member_type = #form.membertype#
</CFIF>
<CFIF IsDefined("form.company") AND #form.company# NEQ "">
AND company_name LIKE '%#form.company#%'
</CFIF>
<CFIF IsDefined("form.coating_search")><!--- If the user selected a
coating search option then check for coatins --->
<!--- Loop through every coating that the user multiselected on
the search form --->
AND coatings CONTAINS '#ListFirst(form.coatings)#'
<!---DEBUGGING <CFLOOP list="#form.coatings#" index="coating">
OR coatings = '#coating#'
</CFLOOP> DEBUGGING --->
</CFIF>
</CFQUERY>
I was just testing the correct syntax with just checking for one element
of the form field list. But I keep getting syntax errors.
How can I accomplish this??
-Gel
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
