database : related fields contain multiple value

Question

Here's the table1 :

| code | fruit  |
|  1   | Orange |
|  2   | Apple  |
|  3   | Grape  |
|  4   | Mango  |

Here's the table2 :

| name   | fruitcode |
| Jack   | 1, 3      |
| Alice  | 2         |
| Emma   | 1, 2, 4   |

My question is, can table2.fruitcode which contains numbers relate to table1.code ?

Normalize your database. Read up on how to model a [many-to-many](https://duckduckgo.com/?q=sql+many+to+many) table relationship in your DBMS. — Igor, Jun 21 '19 at 20:19
some people say `WHERE field REGEXP ',?[SEARCHED-VALUE],?';` [check it](https://stackoverflow.com/questions/471914/can-you-split-explode-a-field-in-a-mysql-query#answer-10953029) — skobaljic, Jun 21 '19 at 21:44

score 0 · Answer 1 · answered Jun 21 '19 at 20:25

If by relate you mean to join the tables, you can use the LIKE operator:

select *
from table1 t1 inner join table2 t2
on concat(',', replace(t2.fruitcode, ' ', ''), ',') like concat('%,', t1.code, ',%')

Most databases support the functions concat() and replace().
But this is just a solution for the specific problem.
Soon you will face problems that can't be dealt with such simple tricks.
The best solution is to normalize your tables from the start.

database : related fields contain multiple value

1 Answers1