the option to modify the db, I would. Replace all lists by related
tables!! It would make this kind of operation very easy.
Now, as I know people don't like to hear: "change the db", because
sometimes they can't: here is the solution to your problem.
<cfset form.group1="1,7">
<cfset form.group2 = "3">
<cfset form.group3 = "4">
<cfquery name="qResult" datasource="#request.dsn#">
SELECT thedata
FROM thetable
WHERE 0 = 0
<cfloop list="#form.group1#" index="i">
AND ',' + group1 + ',' LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%,#i#,%">
</cfloop>
<cfloop list="#form.group2#" index="i">
AND ',' + group2 + ',' LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%,#i#,%">
</cfloop>
<cfloop list="#form.group3#" index="i">
AND ',' + group3 + ',' LIKE <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="%,#i#,%">
</cfloop>
</cfquery>
Pascal
> -----Original Message-----
> From: Howie Hamlin [mailto:[EMAIL PROTECTED]
> Sent: donderdag 25 maart 2004 14:58
> To: CF-Talk
> Subject: SQL Q
>
> We have an MSSQL/2000 database where several columns contain
> comma-delimited lists of data. For example:
>
> Sample row from table:
> Group1=1,5,7,9
> Group2=3,8
> Group3=4
>
> We want to allow someone to search the database based on
> these columns so that if someone searched on
>
> Group1=1,7
> Group2=3
> Group3=4
>
> Then it would find the sample row shown above. It would also
> return that row in the results if the user chose:
>
> Group1=1
> Group2=8
> Group3=4
>
> But, if the user selects:
>
> Group1=1,2,5,7,9
> Group2=3,8
> Group3=4
>
> Then that row would not be included in the results.
>
> I'm having a heck of a time trying to figure out how to write
> the SELECT statement for this. Any ideas?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
