Left Join showing multiple values from the joined table

Question

Having this SQL:

create table Team(id integer, attacker int, midfield int, defender int);
insert into Team(id, attacker, midfield, defender) values(1, 50, 51, 51);
insert into Team(id, attacker, midfield, defender) values(2, 50, NULL, 55);
insert into Team(id, attacker, midfield, defender) values(12, 50, 53, NULL);


create table Footballer(id integer, name varchar(100));
insert into Footballer(id, name) values (50, "AbbaDude");
insert into Footballer(id, name) values (51, "BravoDude");
insert into Footballer(id, name) values (52, "Charlie");
insert into Footballer(id, name) values (53, "Dude");
insert into Footballer(id, name) values (54, "Elfonso");
insert into Footballer(id, name) values (55, "Fix");

select t.id, t.attacker, t.midfield, t.defender, f.name from Team t 
left join Footballer f on f.id = t.id

I get this:

id  attacker    midfield    defender    name
1   50          51          51          NULL
2   50          NULL        55          NULL
12  50          53          NULL        NULL

And instead of the numbers (50, 51,55, etc) I want to see the names from table Footballer.

id  attacker    midfield    defender    
1   AbbaDude    BravoDude   BravoDude   
2   AbbaDude    NULL        Fix         
12  AbbaDude    Dude        NULL        

Answer 1

You can use this query:

select t.id, at.name, md.name, df.name 
from Team t 
left join Footballer at on at.id = t.attacker
left join Footballer md on md.id = t.midfield
left join Footballer df on df.id = t.defender