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

[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

 

[r937]

this will perform terribly no matter how you try to optimize it, because the real problem here is the comma-delimited list stored in the database

take your passed list and use list functions with a loop in order to extract each item in the passed list

slap commas around each item, and then slap percent signs around the commas

then concatenate commas to the front and back of ColumX, and compare to each wildcard term

 where ','||ColumX||',' like '%,2,%'
    or ','||ColumX||',' like '%,5,%'
    or ','||ColumX||',' like '%,10,%'
    or ','||ColumX||',' like '%,6,%'

r937.com | rudy.ca

 

[cfStarlight]

frank11,

In case you're new to databases, storing delimited data that way should be avoided whenever possible. It violates normalization rules and results in poor performance. Not to mention the fact that querying the data is difficult and error prone. I strongly recommend redesigning your table.

One-to-many or many-to-many relationships should be stored in a separate table. With each pair of ids entered as a separate row.

TableOneID | ColumnXID
-------------------------
1 | 3
1 | 5
1 | 21
1 | 4
2 | 1
2 | 4
2 | 7
2 | 9
...

Now your query is simple

SELECT *
FROM TheNewTable
WHERE ColumnXID IN (2,5,10,6)

 

[GUJUm0deL]

SELECT *
FROM TheNewTable
WHERE ColumnXID IN (2,5,10,6)

Of course using SELECT * should also be avoided.

____________________________________
Just Imagine.

http://www.imajinarts.com

 

[cfStarlight]

Yes, obviously.