i have two tables

tb1

tb1_id          -       store_ids      -           date
  1             -        1,2,3,4       -        2023-01-01
  2             -            3,4       -        2023-06-01

tb2

tb2_id          -       name      -              date
 1              -       gold      -           2023-01-01
 2              -       mond      -           2023-01-01
 3              -       burgar    -           2023-01-01
 4              -       glass     -           2023-01-01
 5              -       blackD    -           2023-01-01

what i have tried is

sql
SELECT * 
FROM `tb2`
JOIN `tb1`
WHERE `tb2_id` IN (`store_ids`)

and i get error

'Warning: #1292 Truncated incorrect INTEGER value: 1,2,3,4'

CodePudding user response：

You can use find_in_set

select * from tb1 join tb2 on find_in_set(tb2_id ,tbl1_id)

But as I mentioned in my earlier comment, it is better to redesign your table

DEMO

CodePudding user response：

You could try below query

SELECT * 
FROM `tb2`
JOIN `tb1`
WHERE `store_ids` REGEXP CONCAT('[[:<:]]',`tb2_id`,'[[:>:]]') -- MySQL 5.6
-- For MySQL 8.0, using WHERE `store_id` REGEXP CONCAT('\\b',`tb2_id`,'\\b')  

But it's better to not store foreign keys as list of ids separated by comma.