Brian:
You probably want o be using wildcard matching instead of the IN operator.
Your query should be changed to something like:
WHERE (
Emonth LIKE '%,#frmEMonth#,%'
OR Emonth LIKE '#frmEMonth#,%'
OR Emonth LIKE '%,#frmEMonth#'
OR Emonth = '#frmEMonth#'
)
AND ...
The first line matches records that contain the number in the middle of the
field (1,3,2). The second line matches records that contain the number at
the beginning of the field (3,1,2). The third line matches records that
contain the number at the end of the field (1,2,3). The last line matches
records that only contain that one number (3).
Note that the above code assumes there are no spaces between the commas and
the numbers. The reason why the commas are included is to make sure that a
search for "3" doesn't return results that contain but don't equal 3 (ex.
13, 32, etc.).
The IN operator is used when trying to match a field to any number of
results. For example, to check a field "myField" to see if it contains any
of the numbers from 1 to 3, you'd use code like:
WHERE myField IN (1,2,3)
--
Mosh Teitelbaum
evoch, LLC
Tel: (301) 942-5378
Fax: (301) 933-3651
Email: [EMAIL PROTECTED]
WWW: http://www.evoch.com/
> -----Original Message-----
> From: Brian Ferrigno [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 13, 2003 1:04 PM
> To: CF-Talk
> Subject: SQL problem using the IN function
>
>
> Hello all,
>
> I am attempting to return all rows from a table by
> searching for an integer value in a varchar column
> that contains a comma-seperated list of numbers.
>
> Right now the query is only returning rows that are
> single values and not part of a comma-seperated list.
> For example if I try to look for records that contain
> a value of 3 in the EMonth column:
>
> ID EMonth
> ----------
> 1 1
> 2 3
> 3 1,2,3
>
> only row 2 will be returned. The query ignores row 3
> and any other row with a comma-sep list.
>
> THE CODE
> --------
> SELECT ID as cardid, CardName, ELogo, Monthly, ECatID
> FROM ECards
> WHERE '#frmEMonth#' IN (Emonth) AND monthly=1
> <cfif fSubcatID>AND
> eSubcatID=#fSubcatID#</cfif>
> <cfif fCatID>AND ECatID=#fCatID#</cfif>
> ORDER BY sortCard
> --------
>
> Thanks for any help.
>
>
> Brian
>
>
>
> __________________________________
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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
