How To use inner Join with two table where one table has id in array ["11", "12"]

Question

I have two tables

Table promos - which has contract table id like ["11,"12"]

Table contract - which has id reference name.

I am trying to get the name from contract table with below sql query but showing error in query

SELECT * FROM promos INNER JOIN contract ON promos.contracts_id=contract.id

Expected Result

Please suggest, how to get this type of ID with query without change IN TABLE.

it is never a good idea to store foreign keys in this way. read about normalization — Jens, Jul 26 '22 at 06:28
Use JSON_CONTAINS() in ON clause, for example. Or any other searching JSON function. But the best solution is to normalize your data. — Akina, Jul 26 '22 at 06:29
Does this answer your question? [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — ADyson, Jul 26 '22 at 06:44

score 0 · Answer 1 · answered Jul 26 '22 at 12:28

You can use next query (match records using JSON_CONTAINS and after this group them using JSON_ARRAYAGG:

SELECT contracts_ids, JSON_ARRAYAGG(name)
FROM promos 
LEFT JOIN contract ON JSON_CONTAINS(contracts_ids, CONCAT('"', contract.id, '"'))
GROUP BY promos.id, contracts_ids;

Online SQL editor

How To use inner Join with two table where one table has id in array ["11", "12"]

1 Answers1