How to compare value with csv value in mysql?

Question

Table 1

id(int) | name(varchar) 1 | at,bat 2 | cat,at,bat,mat 3 | mat,cat 4 | sat,bat 

table 2

 id(int) | type(varchar) 1 | at 2 | mat 

As you can see, table1 contains the csv rows. Now I need to get the id from Table 1 , whose name exists in the field of type Table2 .

Is there any clean way to query mysql? if not, what would be the most efficient way to do this in the case of large recordsets?

Answer 1

I would use FIND_IN_SET(str,strlist) :

 select distinct t1.id from table1 t1 join table2 t2 on find_in_set(t2.type, t1.name) > 0 

Working example: http://sqlfiddle.com/#!2/b642c/4

Answer 2

Look here

 select a.ids as id1, b.ids as id2, a.name,b.type from table1 a inner join table2 b on find_in_set(b.type,a.name)