Left Join showing multiple values from the joined table-CodePudding

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

CodePudding user response：

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

CodePudding user response：

You need to join the table Footballer three times, so we use aliases to know which join we are referring to. I like to use one or two letter aliases, but a letter which lets you know what you are referring to.
Here a for attacker, m for midfield and d for defender.

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

id | attacker | midfield  | defender 
-: | :------- | :-------- | :--------
 1 | AbbaDude | BravoDude | BravoDude
 2 | AbbaDude | null      | Fix      
12 | AbbaDude | Dude      | null

db<>fiddle here

CodePudding user response：

You need temId value in Footballer table. So, yo must use this codes:

create table Footballer(id integer,teamId integer , name varchar(100))
insert into Footballer(id, name) values (50,[teamId], "AbbaDude");
insert into Footballer(id, name) values (51,,[teamId], "BravoDude");

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

all codes:

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,teamID INTEGER, name varchar(100));
insert into Footballer(id,teamID, name) values (50,1, 'AbbaDude');
insert into Footballer(id,teamID, name) values (51,2, 'BravoDude');
insert into Footballer(id,teamID, name) values (52,1, 'Charlie');
insert into Footballer(id,teamID, name) values (53,12, 'Dude');
insert into Footballer(id,teamID, name) values (54,12, 'Elfonso');
insert into Footballer(id,teamID, name) values (55,1, 'Fix');

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