MySQL 8.0

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

CREATE TABLE a
    (`id` int, `name` varchar(5))
;
    
INSERT INTO a
    (`id`, `name`)
VALUES
    (1, 'John'),
    (2, 'Mark'),
    (3, 'Helen'),
    (4, 'Simon')
;
CREATE TABLE b
    (`id` int, `day` varchar(3), `team` varchar(5), `amount` int)
;
    
INSERT INTO b
    (`id`, `day`, `team`, `amount`)
VALUES
    (1, 'mon', '2,4', 100.00),
    (2, 'mon', '1,4', 80.00),
    (3, 'fri', '2,3', 150.00),
    (4, 'sun', '1,3,4', 120.00),
    (5, 'wen', '3', 40.00)
;
select 
b.*
, (select group_concat(a.name ) 
from a 
where FIND_IN_SET(a.id, b.team) > 0) as teamname 
from b


id
day
team
amount
teamname


1
mon
2,4
100
Mark,Simon

2
mon
1,4
80
John,Simon

3
fri
2,3
150
Mark,Helen

4
sun
1,3,4
120
John,Helen,Simon

5
wen
3
40
Helen

id	day	team	amount	teamname
1	mon	2,4	100	Mark,Simon
2	mon	1,4	80	John,Simon
3	fri	2,3	150	Mark,Helen
4	sun	1,3,4	120	John,Helen,Simon
5	wen	3	40	Helen