Mysql commaseparated column matching from another table

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? :)

php
mysql

2012-04-04 21:30
by teecee

you can't do that you have to explode the contents of the database and convert them to string or array and use that to search - magicianiam 2012-04-04 21:34

you need to do where 1 IN(...) and 2 IN (...) and 3 IN(...)binarious 2012-04-04 21:34

Posting the whole query and the error you get would make it easier to solve your proble - NoName 2012-04-04 21:34

You could also normalize your database and not save a comma-separated list as one column. Use a seperate table with foreign keys. This would make your query way easier und more efficient - Basti 2012-04-04 21:36

Just to be sure: There are no comma-separated list types in MySQL, so I assume 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

I think i better normalise database, because i will run into this problem again and again :) Learnt my lesson here..

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.

2012-04-04 21:36
by Marc B

+1 for the part about the normalization - Basti 2012-04-04 21:38

I will normalize! :) Thanks for good answear - teecee 2012-04-04 23:30