how to join one column to all columns from another table

I have two tables, players and lineup table with all players in the team

the lineup table have this columns

 `match_id`, `goalkeeper`, `center_back`, `center_back_2`, `right_outside_back`, `left_outside_back`, `defensive_center_midfielder`, `center_midfielder`, `attacking_center_midfielder`, `right_winger`, `left_winger`, `center_forward`

and all of the columns have the ID of the one player in the players table

How to join all the columns here to the player table ?

Answer

something in line with this:

select match_id, 
gk.player_name as goalkeeper, 
c_back.player_name as center_back 
from matches
left join players as gk on gk.id = matches.goalkeeper
left join players as c_back on c_back.id = matches.center_back
...
where match_id = x

basically a lot of left_joins.

If you redesing your database model and add table that will have columns: match_id, position_id, player_id things will be much simpler and database engine will be grateful to you.

then your SQL would look much simpler:

select positions.* from position_definition as positions
left join (select mp.*, players.player_name from  match_players as mp left join players on mp.player_id = players.id where match_id = x) 
as team on team.position_id = positions.id

(something like that, did not check for accuracy here) there is additional table added holding a list of all positions in a match.