CODEWITHSUNDEEP
sql
spyk
spyk

Reputation: 898

SQL query on table that has 2 columns with a foreign id on the same table

I have a table let's say in the form of: match(id, hometeam_id, awayteam_id) and team(id, name). How do I build my SQL query in order to get a result table in the form of (match_id, hometeam_name, awayteam_name), since they both (hometeam_id, awayteam_id) reference the same table (team)?

Thank you

Upvotes: 0

Views: 216

Answers (4)

Thyamine
Thyamine

Reputation: 1258

select m.id, h.name as hometeam_name, a.name as awayteam_name
from match m left join team h on m.hometeam_id = h.id
left join team a on m.awayteam_id = a.id

Upvotes: 0

Paul Smith
Paul Smith

Reputation: 3216

Join to the team table twice, once for the Home Team, and again for the Away Team, using aliases after the table names in the query:

select m.match_id, homeTeam.name as HomeTeamName, awayTeam.name as AwayTeamName
from 
team homeTeam join
match m on m.hometeam_id = homeTeam.hometeam_id join
team awayTeam on awayTeam.hometeam_id = m.awayteam_id

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60498

You would just join to the team table multiple times:

SELECT m.id, away.name, home.name
FROM match m
INNER JOIN team away ON away.id = m.awayteam_id
INNER JOIN team home ON home.id = m.hometeam_id

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

You join to the team table twice.

select matchdate, t1.teamname, t2,teamname from 
match m 
join team t1 on m.hometeamId = t1.teamid
join team t2 on m.awayteamid = t2.teamid

Upvotes: 0

Related Questions