SQL join multiple "id" columns from a single row to another table Apologies if this has been asked but I've tried searching and haven't come across anything similar. I have a two tables in a MySQL database. Athletes and Teams

The athletes table contains the following columns:


athleteID
firstName
lastName


and the Teams table consists of:


teamID
athleteID_1
athleteID_2
athleteID_3
athleteID_4


Given a teamID, I would like to retrieve the firstName, lastName, and athleteID of all the athletes on the team.

Currently, I am doing five queries 

SELECT athleteID_1, athleteID_2, athleteID_3, athleteID_4 FROM Teams WHERE teamID=3


Then using a for loop in PHP to run the following query four times (once for each athleteID:

SELECT firstName, lastName from Athletes where athleteID=$athlete_id_n


I would like to end up with a four arrays, each with two entries (firstName and lastName).
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
;