matching comma separated values in MySQL on join

Question

I have a few tables i need to join, i cannot change the database as it's being used in multiple different applications.

The first table looks like the following

id, list_of_foreign_id


1, "1, 3, 4, 8, 12, 13, 14, 15, 16, 23"

2, "1, 3, 4, 8, 23"

3, "1, 3, 8, 12, 13, 14, 15, 23"

4, "7"

....

And the forrening table is simply joined on ID and could look like the following

ID, name

1, "john Oliver"

2, "John Doe"

3, "Arthur Dent"

....

And i need to match the list of ids in table to to the id on table two.

The query is going to look like the following but i'm unsure about the join condition

Select ...
from
A
join
B on a.list_of_foreign_id contains B.id

I'm thinking it can be done using regex.

I've tried with locate, but it also matches sub-strings like 1, 3 will match to 13. etc.

I've also looked around and found this post, Comma Separated Numbers Regex But I'm unsure how to look with regex for specific cases.

Commalists are usually a 'bad schema design'. – Rick James Jul 02 '18 at 15:12 — Rick James, Jul 02 '18 at 15:12

score 1 · Accepted Answer · answered Jul 02 '18 at 09:35

1

SELECT *
       FROM A
            INNER JOIN B
                       ON find_in_set(B.id, A.list_of_foreign_id);

answered Jul 02 '18 at 09:35

sticky bit

This doesn't work in practice as it only returns the first match. – Doommius Jul 02 '18 at 22:31
And? One match is enough to decide that "`a.list_of_foreign_id contains B.id`" as you requested. – sticky bit Jul 02 '18 at 22:34

1 Answers1