Dylan,
Thanks for this suggestion. This doesn't do what is needed though I don't
think. My trouble is I am comparing one list of values against another list
of values. Take the following situation:
1. The string I want to compare against is this: 1,3,5
2. The field (FLD) I am comparing it against contains this value: 2,4,10
If I use IN, it would look like this:
WHERE '2,4,10' IN('1,3,5')
What I _WANT_ to do is this (in layman's terms):
get all records where (1 = 2 or 1 = 4 or 1 = 10) OR (3 = 2 or 3 = 4 or 3 =
10) OR (5 = 2 or 5 = 4 or 5 = 10)
I'm comparing discrete values in the comparison list against discrete values
in the the field value list. Only if one of the discrete values matches do I
want to the record.
Any ideas on how to accomplish this?
Thanks for ideas!
Paul Sinclair
> -----Original Message-----
> From: Dylan Bromby [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 11:01 AM
> To: CF-Talk
> Subject: RE: Comparing value against a list of values
>
>
> Paul,
>
> You should use the IN operator in your WHERE clause. I'm not clear on your
> data structure from your post, but try something like the following (make
> sure the list is in parentheses);
>
> SELECT
> *
> FROM
> tbl
> WHERE
> csvList IN (10,11,21,100)
>
> -----Original Message-----
> From: Paul Sinclair [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 13, 2001 7:17 AM
> To: CF-Talk
> Subject: SQL: Comparing value against a list of values
>
>
> I have a table with a field that contains a comma delimited list
> of values,
> such as:
>
> 10,11,21,100
>
> In doing a <cfquery> against the table, I want to select records where one
> or more of the values in this list of values matches a value in the WHERE
> clause of my sql query. For example:
>
> select *
> from tbl
> where csvList = '%1%'
>
> The problem is that the above select statement will select ANY record that
> has '1' in the field no matter what the "true" values in the field are
> (i.e., 1 is "LIKE" 10,11,21,100).
>
> How do I make the select query compare '1' against '10', and '1' against
> '11' and '1' against '21', and '1' against '100'.
>
> Thank you for help.
>
> Regards,
> Paul Sinclair
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
