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.
CodePudding user response:
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;