MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE actus (id INT, theme TEXT);
Insert INTO actus VALUES (1, "1,2,3");
Insert INTO actus VALUES (2, "4,5,6");
Insert INTO actus VALUES (3, "7,8,9");
Insert INTO actus VALUES (4, "1,3,5");
Insert INTO actus VALUES (5, "7,9");
Insert INTO actus VALUES (6, "2,4,6");
Insert INTO actus VALUES (7, "8");
SELECT * FROM actus;


id
theme


1
1,2,3

2
4,5,6

3
7,8,9

4
1,3,5

5
7,9

6
2,4,6

7
8


CREATE TABLE themes (id INT, theme TEXT);
Insert INTO themes VALUES (1, "Theme1");
Insert INTO themes VALUES (2, "Theme2");
Insert INTO themes VALUES (3, "Theme3");
Insert INTO themes VALUES (4, "Theme4");
Insert INTO themes VALUES (5, "Theme5");
Insert INTO themes VALUES (6, "Theme6");
Insert INTO themes VALUES (7, "Theme7");
Insert INTO themes VALUES (8, "Theme8");
Insert INTO themes VALUES (9, "Theme9");
Insert INTO themes VALUES (10, "Theme10");
SELECT * FROM themes;


id
theme


1
Theme1

2
Theme2

3
Theme3

4
Theme4

5
Theme5

6
Theme6

7
Theme7

8
Theme8

9
Theme9

10
Theme10


  Create view actus_theme as 
  SELECT actus.id actusid, jsontable.theme themeid
  FROM actus
  CROSS JOIN JSON_TABLE(CONCAT('["', REPLACE(theme, ',', '","'), '"]'),
                      '$[*]' COLUMNS (theme TEXT PATH '$')) jsontable;
select * from actus_theme;


actusid
themeid


1
1

1
2

1
3

2
4

2
5

2
6

3
7

3
8

3
9

4
1

4
3

4
5

5
7

5
9

6
2

6
4

6
6

7
8


select * from themes where themes.id IN (select themeid from actus_theme where actusid = 2)


id
theme


4
Theme4

5
Theme5

6
Theme6

id	theme
1	Theme1
2	Theme2
3	Theme3
4	Theme4
5	Theme5
6	Theme6
7	Theme7
8	Theme8
9	Theme9
10	Theme10