I would like to select all matches from a commaseparated column in table2, where column could be like this: 0,1,2 OR 2,4,5 OR 2,5 OR 1,3,5 etc.
I have tried with:
SELECT * from table where 1,3,5 IN(SELECT commaseparated FROM table2) WHERE ..
But error on statement when using commas.
I've also tried using REGEXP but in my case i need to search for all matches within 1,3,5
How can i solve this one? :)
where 1 IN(...) and 2 IN (...) and 3 IN(...)
binarious 2012-04-04 21:34
commaseparated
is of type string. You don't want SELECT * from mytable where '1,3,5' IN(SELECT commaseparated FROM table2)
, do you? (added quotes around your list, to make it a string - Basti 2012-04-04 21:39
Thanks so much for help all! - teecee 2012-04-04 22:07
Can't do that in standard SQL. it's
WHERE singlevalue IN (list, of, values)
if you want to compare lists against lists, you should revamp your tables so they're properly normalized. Storing formatted data in a field basically negates the purpose of having a relational database - you can't establish relationships with the data if it's not in a format that allows relationships to be formed.
If those CSV lists were in sub-tables, you could do a very simple JOIN query to meet your specifications.