SQL statement from comma separated list

I'm passing multiple select box entries to a single database field and want to use them in another query. So far I've put them in a comma separated list and submit them to the table. This works fine. The next step for me is a little trickier. I now want to take that list and use it in a where statement.

An example will help I think. Lets say in a table named mailing_list I have a column called town. This is the column thats passed the values from the multiple select box. Hypothetically this could contain town1,town2,town3 OR if the user only selects one town it might contain the value town1.

I want to use this value in the following SQL query
<cfquery
datasource="#request.dsn#"
name="mailing_list"
SELECT
email
FROM
client
WHERE
town = 'town1'
</cfquery>

The problem is if the user selects more than one town, the WHERE statement needs to read

WHERE
town = 'town1' AND town = 'town2' AND town = 'town3'

or.......

WHERE
town = 'town1' AND town = 'town2'

if only two are selected. Is there a way of dynamically coding this so the correct number of AND's are inserted, if any are required at all?

TIA
Mark

first of all, you want ORs instead of ANDs

but you can get around this real easy like this:

... WHERE town IN ( #ListQualify(form.townselect,"'",",","ALL")# )

hi Rudy (and of course, OR not AND..*slaps forehead*). In that case I'm going to have to convert the town field of the query back to a list, since the values aren't coming directly from the form. The reason I can't use the initial list is because the values are sent to a popup window (and that's one of the reasons they've been entered into a database, but not the only reason). Thanks for the quick response.

adieu
Mark

hrmm....I tried this. Initial query checking for multiple town entries.... (along with others)
<cfquery datasource="#request.dsn#" name="mailbomb">
SELECT mail_details.subject,
mail_details.table_name,
mail_details.mail_format,
mail_details.mail_content,
mail_details.town
FROM mail_details
WHERE id = (SELECT Max(id) FROM mail_details)
</cfquery>
<cfset attributes.mailbomb = mailbomb>


and later when emailing,

<cfquery
datasource="#request.dsn#"
name="MailList"
cachedwithin="#CreateTimeSpan(0,1,0,0)#">
SELECT
email
FROM
#attributes.mailbomb.table_name#
WHERE
town IN ( #ListQualify(attributes.mailbomb.town,"'",",","ALL")# )
</cfquery>


Seems to be working...thanks again! I need to buy a decent SQL book that covers beginners to advanced. I know some things, but clearly not enough. Any recommendations?

i don't understand how you can get multiple towns from the mailbomb query, when in fact you are selecting only the highest id

but i'm glad it's working for ya