> 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?
You can't use IN on a contiguous string, i.e. a single row of a given table
column... You would need to have the values in your comma delimited list
also in a 2nd table, which is fairly easy to accomplish with a little cf
<cfquery name="rsitems">
SELECT itemid, keywordids FROM mytable
</cfquery>
<cfloop query="rsitems">
<cfif len(trim(rsitems.keywordids))>
<cfquery>
insert into myxreftable ( itemid, keywordid )
select #rsitems.itemid#, keywords.keywordid
from keywords where keywords.keywordid in (#rsitem.keywordids#)
and not exists (select id from myxreftable
where itemid = #rsitems.itemid#
and keywordid = keywords.keyworid)
</cfquery>
</cfif>
</cfloop>
Once you've got all your data into the cross reference table, you should be
able to easily accomplish your keyword search based on your keywords table
contents... As a rule of thumb, you probably don't want to store lists of
id's in varchar columns in your database. I have one database which does
this in one particular table as a matter of denormalization, however, the
values in the column are _also_ stored in a cross-reference table and both
are updated in only one location in my application, so I don't have to worry
too much about discrepancies between the string column and the
cross-reference table...
S. Isaac Dealey
Certified Advanced ColdFusion 5 Developer
www.turnkey.to
954-776-0046
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
