Steve,
This has almost got it. I'm having a problem now matching when a record in
the table is NOT a single value. I.e., if the record has a fld1 value of
"4", the loop thing does fine. But if fld1 has a value like "12,14,16,18",
this will get selected if the comparelist is "1,2,4,6,8" (1 matches all 4; 2
matches 12; 4 matches 14; etc.)
It's the " fld1 LIKE '#i#' " comparison that's the issue right now. I
changed it to " fld1 LIKE '%#i#%' " so that it would match records where the
fld1 value is NOT a single value. But I need to tell it "the value you are
comparing is the value delimited by commas in fld1." I'm trying different
ways to get around it but so far no luck. If you have any ideas, let me
know.
As I read over this, I realize it's none too clear, but I'm having
difficulty expressing the difficulty I'm having! Sheesh.
Thanks for your help already - this got me off dead center!!
Regards,
Paul Sinclair
> -----Original Message-----
> From: Steve Robison, Jr. [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 12, 2001 3:39 PM
> To: CF-Talk
> Subject: Re: Comparing db field list to second list
>
>
>
>
> Put this WHERE statement in your query. change fldl to whatever
> the field is called.
> <CFSET comparelist = "1,8,100">
>
> WHERE (<CFLOOP INDEX="i" LIST="#comparelist#"
> DELIMITERS=","><CFIF IsDefined("flag")>OR <CFELSE><CFSET flag =
> "on"></cfif>(fldl LIKE '#i#')</cfloop>)
>
> On Jun 12, 2001 at 2:25 PM, you wrote...I have a db table with a
> text field that has comma separated values, such
> > as:
> >
> > fld1
> > ----------------------
> > Record 1| 1,3,5,7 |
> > Record 2| 2,4,6,8 |
> > Record 3| 100,120 |
> >
> > I also have a list (call it "comparelist") such as:
> >
> > 2,8,100
> >
> > I want to pull all records from the database where a single value in the
> > fld1 list is contained in the comparelist. For example, given the above
> > data, Record 2 and Record 3 would be retrieved. Basically I'm
> comparing two
> > csv lists to each other.
> >
> > How would I design a select query to accomplish this? If the db were
> > designed so that fld1 contained only 1 discrete value, I could
> use the IN
> > function (along the lines of: where fld1 IN(comparelist) ). But in this
> > situation (and it is not an option to redesign the db) I somehow need to
> > compare each value in the db field to comparelist.
> >
> > Thanks for help.
> >
> > 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/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
