0

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
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Does this answer your question? [How does a MYSQL Self-Join Work?](https://stackoverflow.com/questions/1284441/how-does-a-mysql-self-join-work) – philipxy Mar 15 '22 at 10:55
  • Anyway this is a duplicate of the very basic idea of a many:1 join, a duplicate case of which is to get a name from an id, a duplicate case of which involves multiple ids/names, a duplicate case of which involves the same table more than once, eg [Query to get name from a table with 2 ID columns](https://stackoverflow.com/q/4308644/3404097) PS [How to get matching data from another SQL table for two different columns: Inner Join and/or Union?](https://stackoverflow.com/q/27682228/3404097) [What is a self join for? (in english)](https://stackoverflow.com/a/37384306/3404097) – philipxy Mar 15 '22 at 12:19

3 Answers3

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
Hamed Naeemaei
  • 8,052
  • 3
  • 37
  • 46
1

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

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Thanks, as I mentioned below to Hamed, unless there is something better than 3 left-joins, this should be the correct answer. – Vityata Mar 15 '22 at 10:29
0

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
Mehmet Arlı
  • 62
  • 4