How to SELECT with WHERE condition 'IN' referring to text?

Question

I have 2 tables.

Table_A keeps reference id (id_ref) int as PK.

Table B stores history of the changes by keeping many id_ref in a field as text. example: 1,2,3 in this text field

How can I select directly using select * from A where id_ref IN (select id_ref from table B)?

Example query as below:

SELECT name FROM TABLE_A WHERE id_ref IN ( SELECT id_ref FROM TABLE_B WHERE id=4097 );

I can use function explode() in PHP to split and get the the individual value. But is there any ways I can directly query without using split function.

Answer 1

The subquery:

SELECT id_ref FROM TABLE_B WHERE id=4097

returns a string which is a comma separated list of integers and can't be used with the operator IN.

You need the function FIND_IN_SET():

SELECT name 
FROM TABLE_A 
WHERE FIND_IN_SET(id_ref, (SELECT id_ref FROM TABLE_B WHERE id = 4097));

or:

SELECT a.name 
FROM TABLE_A a INNER JOIN TABLE_B b
ON FIND_IN_SET(a.id_ref, b.id_ref)
WHERE b.id = 4097;