Question 21106 in Mysql Question posted in
The official MySQL documentation can be found here.

selecting data where column is array – Mysql

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’

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search