SQL join multiple "id" columns from a single row to another table
You could use a union. Fiddle

select * from (
  select t.teamID, a1.firstName, a1.LastName, 1 as AthleteNumber from teams t
  inner join athletes a1 on a1.athleteID = t.athleteID_1
  union
  select t.teamID, a2.firstName, a2.LastName, 2 as AthleteNumber from teams t
  inner join athletes a2 on a2.athleteID = t.athleteID_2
  union
  select t.teamID, a3.firstName, a3.LastName, 3 as AthleteNumber from teams t
  inner join athletes a3 on a3.athleteID = t.athleteID_3
  union
  select t.teamID, a4.firstName, a4.LastName, 4 as AthleteNumber from teams t
  inner join athletes a4 on a4.athleteID = t.athleteID_4
) s
order by teamID, AthleteNumber
;