你可以试着 JOIN 在 dev_user 两张table。
一个给 dev_test_teams_athlete table。
另一个给我 dev_test_team table。

CREATE TABLE dev_test_team(
   team_id int,
   team_name varchar(50),
   user_id int
);

insert into dev_test_team values (1,'indi',480);
insert into dev_test_team values (2,'usa', 472);
insert into dev_test_team values (3,'uk',  765);

CREATE TABLE dev_test_teams_athlete(
   teams_athlete_id int,
   team_id int,
   user_id int
);

insert into dev_test_teams_athlete values (12,1,380);
insert into dev_test_teams_athlete values (23,2,379);
insert into dev_test_teams_athlete values (24,3,479);
insert into dev_test_teams_athlete values (25,3,464);

CREATE TABLE dev_user(
   user_id int,
   first_name varchar(50)
);
insert into dev_user values (480,'coach1');
insert into dev_user values (472,'coach2');
insert into dev_user values (765,'coach3');
insert into dev_user values (380,'athlete1');
insert into dev_user values (379,'athlete2');
insert into dev_user values (479,'athlete3');
insert into dev_user values (464,'athlete4');

查询1：

SELECT coach.first_name 'first_name(coach_name)',
       athlete.first_name 'first_name(athlete_name)',
       dtt.team_id,
       dtt.team_name
FROM dev_test_team dtt 
INNER JOIN dev_test_teams_athlete  dtta on dtt.team_id = dtta.team_id
LEFT JOIN dev_user coach on dtt.user_id = coach.user_id
LEFT JOIN dev_user athlete on dtta.user_id = athlete.user_id

结果：

| first_name(coach_name) | first_name(athlete_name) | team_id | team_name |
|------------------------|--------------------------|---------|-----------|
|                 coach1 |                 athlete1 |       1 |      indi |
|                 coach2 |                 athlete2 |       2 |       usa |
|                 coach3 |                 athlete3 |       3 |        uk |
|                 coach3 |                 athlete4 |       3 |        uk |