SQL Query - how to contruct a WHERE clause

[drmaves]

I have a field in a table that contains a comma separated list that I want to
be able to determine whether or not any of the items in the list are contained
in another list.

For example:

SELECT *
FROM idx_res
WHERE SelectListingAlert.types IN idx_res.types

Actual data might look like this:

WHERE (RES,HRS,WTR) IN (RES,WTR)

I need to contruct a WHERE clause that takes each item in the first list
(SelectListingAlert.types) and see if it exists in the second list
(idx_res.types). If it exists it should be selected. Each item in the first
list would be treated as OR so if RES or HRS or WTR exists in the second list
it should be selected.

Can someone direct me on what the best way to approach this is?

Thanks.

[BKBK]

>... takes each item in the first list (SelectListingAlert.types) and see if it
exists in

>... the second list (idx_res.types). If it exists it should be selected.

Each item in

>... the first list would be treated as OR so if RES or HRS or WTR exists in

the

>... second list it should be selected.
>... the best way to approach this is?

No matter what solution you come up with, chances are, it will be different
for MySQL, MS SQL and Oracle, just to name three. I think the "best way to
approach this" is to do the list calculations in Coldfusion, and then use
the result in the where-clause of your query.

[Dan Bracuk]

Create a variable for your where clause. Use list functions on SelectListingAlert.types to build it..

Then use the variable in your query.