FREE AI-Enhanced Online MariaDB Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE main_table(id INT PRIMARY KEY NOT NULL, to_join INT NOT NULL);
CREATE TABLE joined_table(id INT PRIMARY KEY NOT NULL, int_join JSON NOT NULL);
INSERT INTO main_table VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5);
INSERT INTO joined_table VALUES (123, '[2,4]'), (456, '[3,5]'), (789, '[1, 5, 3]');

-- This one splits the join table's values into individual rows
SELECT jt.*, js_t.*
  FROM joined_table AS jt
  CROSS JOIN JSON_TABLE(
    jt.int_join, '$[*]' COLUMNS(
      id INT PATH '$'
    )
  ) js_t;

-- This one joins it to the main table
SELECT mt.id AS main_table_id, jt.id AS joined_table_id, js_t.id AS joined_on
  FROM joined_table AS jt
  CROSS JOIN JSON_TABLE(
    jt.int_join, '$[*]' COLUMNS(
      id INT PATH '$'
    )
  ) js_t
  JOIN main_table mt ON (js_t.id = mt.to_join)
  ORDER BY jt.id;
  
-- This one joins it to the main table
SELECT mt.id AS main_table_id, jt.id AS joined_table_id, js_t.id AS joined_on
  FROM joined_table AS jt
  CROSS JOIN JSON_TABLE(
    jt.int_join, '$[*]' COLUMNS(
      id INT PATH '$'
    )
  ) js_t
  JOIN main_table mt ON (js_t.id = mt.to_join)
  WHERE mt.id IN (1, 5);

id	int_join	id_2
123	[2,4]	2
123	[2,4]	4
456	[3,5]	3
456	[3,5]	5
789	[1, 5, 3]	1
789	[1, 5, 3]	5
789	[1, 5, 3]	3

main_table_id	joined_table_id	joined_on
2	123	2
4	123	4
3	456	3
5	456	5
1	789	1
3	789	3
5	789	5

main_table_id	joined_table_id	joined_on
5	456	5
1	789	1
5	789	5