Help with a query: returning rows from a coma delimited list of integers...

[Frank11]

Hi, I'm rather new to writing queries, so please forgive me if this is a really simple thing to do.

I need to create a CF query that finds out which rows in the DB have ANY of the values that are in a given list. Only one column in each row of the DB has their values stored in a list.

Example:

Passed list = 2,5,10,6

ColumX data in database:
Row 1 = 3, 5, 21, 4
Row 2 = 1, 4, 7, 9
Row 3 = 2
Row 4 = 8, 10, 3, 6
Row 5 = 1, 22, 9, 4

Base on the passed list, the query should return the following rows:
Row 1 (because 5 was in the list)
Row 3 (because 2 was in the list)
Row 4 (because 10 and 6 was in the list)

I’m hoping there’s some SQL function that works with lists and can compare the data sets of two lists to find matching values, but I don’t know.

Can anyone help?
thanks

[acoder]

If it was just one list (and that too, the first list), it would be easy. You could use the IN keyword:

...columnX in (#passedlist#)

Having never needed to program in this way, I wouldn't be able to tell you off the top of my head if this is possible or how to go about solving this.

Which database are you using? What have you attempted so far?