selecting data where column is array

Question

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'

You shouldn't store foreign keys as csv. That is very bad db design — Jens
– Jens, Commented Jan 25, 2023 at 6:50
can you share your db strucure? because your query runs perfectly @diyeco5337 — Bhargav Chudasama
– Bhargav Chudasama, Commented Jan 25, 2023 at 7:05

Jens · Accepted Answer · 2023-01-25 07:29:08Z

2

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

edited Jan 25, 2023 at 7:29

answered Jan 25, 2023 at 7:20

Jens

69.6k15 gold badges107 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Trung Duong · Accepted Answer · 2023-01-25 15:22:25Z

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.

edited Jan 25, 2023 at 15:22

answered Jan 25, 2023 at 7:13

Trung Duong

3,4752 gold badges10 silver badges10 bronze badges

2 Comments

Jens Over a year ago

I think it would not work if you have id's > 10

Trung Duong Over a year ago

@Jens You're correct. I've updated my answer.

Collectives™ on Stack Overflow

selecting data where column is array

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related