SQl join a table on integer and json array for integers

Question

I have two tables with matching column, both of integer types. Let if i want to store multiple id's in second table. the best way i know is json.

Now problem is how to match a integer from one table and json from another table.

example

Tables Countries and another is tours

countries column = id, name (integer) Tours column = id,countries_ids (json), name

I want a join on the basis column countries.id = tours.countries_ids

Storing JSON formatted values in a database is a terrible idea. You should remove the JSON? — zyamys
– zyamys, Commented May 6, 2016 at 6:22
store multiple id's in second table. the best way i know is json.? That is your problem. Don't store multiple id's in 1 row as json. Each id should be in a separate row. Then it is very simple to join the 2 tables. — Sean
– Sean, Commented May 6, 2016 at 6:22
sorry, i am thinking about the same , just few minutes ago and u confirmed it o right time " sean " — Lovepreet Singh Batth
– Lovepreet Singh Batth, Commented May 6, 2016 at 6:26

Dilip Patel · Accepted Answer · 2016-05-06 06:36:19Z

1

if you want you can store coma separate id and in where condition you can use FIND_IN_SET

Select * from table where FIND_IN_SET (int_id, coma_separate_col)

answered May 6, 2016 at 6:36

Dilip Patel

83217 silver badges25 bronze badges

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

1 Comment

This is also a good solution at that time, project is already on the way, so making anther table is bit time consuming, thanks dilip

DD77 · Accepted Answer · 2016-05-06 07:20:45Z

0

You can use Primary Key and Foreign Key concept to store multiple ids in second table.

answered May 6, 2016 at 7:20

DD77

8162 gold badges9 silver badges27 bronze badges