First: put a comma before and after the list you are inserting into the DB.
So a list of "1,2,3,4" becomes ",1,2,3,4,"
Second: use this query:
<cfquery name="query" datasource="#request.DB_DSN#"
dbtype="#request.DB_type#" username="#request.DB_username#"
password="#request.DB_password#">
SELECT title
FROM content
WHERE keywordIDs LIKE '%,23,%'
AND keywordIDs LIKE '%,12,%'
</cfquery>
+-----------------------------------------------+
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: Gyrus [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, October 15, 2002 11:14 AM
To: CF-Talk
Subject: OT: help with SQL 'IN' operator
I'm trying to run a query than uses the IN operator in a way I've not come
across before:
<cfquery name="query" datasource="#request.DB_DSN#"
dbtype="#request.DB_type#" username="#request.DB_username#"
password="#request.DB_password#">
SELECT title
FROM content
WHERE 23 IN (keywordIDs)
AND 12 IN (keywordIDs)
</cfquery>
So, instead of saying "WHERE [a certain field] IN ([comma-separated list of
dynamic values])", I'm looking for records where a dynamic value is IN a
field that contains a comma-separated list of values. I get the error "Data
type mismatch in criteria expression."
I guess this might be an invalid use of IN (i.e. you can only use it with
the DB field on the left of the operator). Doesn't seem like something that
should be invalid - but if it is, how can I achieve the same query?
Gyrus
[EMAIL PROTECTED]
work: http://www.tengai.co.uk
play: http://www.norlonto.net
PGP key available
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription: http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Get the mailserver that powers this list at http://www.coolfusion.com
