MySQL: Using of coma separated ids in a table column and group_concat

Question

In a table, there is a column that contains ids separated by comas ex: "159,167"

I want to do the equivalent of a join between this table and the table that contains those ids.

ex:

TableA             TableB
id desc            id desc tableAids  stock
1  test1           1  abc  1,2,3      1
2  test2           2  def  1,2        0
3  test3           3  ghi  3,4        10
4  test4

How can I join the two tables with a query? I explored the function group_concat() but it returns a string wich I cannot put in a IN clause.

What I am trying to do is something like this:

Select a.desc from TableA a
LEFT JOIN TableB b on a.id = b.tableAids
WHERE b.stock > 1

Answer 1

I have a solution for your question, but:

• you should seriously consider a refactoring to get rid of the coma separated ids

• the solution does what you need right now, but it cannot be optimised (mysql cannot use indexes to speed-up the execution, see below)

Try this:

select a.desc
from TableA a
inner join TableB b on find_in_set(a.id, b.tableAids) > 0
where b.stock > 1;

Mysql cannot use indexes for joining the tables because the columns in the join condition are included in a function. However, if you have an index on TableB.stock column, it might help a little.

Remember: storing multiple values in a column is a big NO-NO and you should get rid of that asap. It will cause many headaches later.