Search code examples
mysqlsqldatabaseinner-join

Inner joins on SQL multiple rows

I've got 2 tables in my SQL database:

**teams**
id
name
country

**games**
id
date
hometeamid (hometeam country)
awayteamid (awayteam country)
score

Now i want to get the games with all their data, but instead of getting hometeamid's I want to get their names and countries by getting them from the teams table by some sort of inner join.

Anyone know how to get 4 variables in 1 query by id from another table (team)?

Solution

  • You should join the table team two time one for hometeam and one for awayteam 

    select g.date, g.hometeamid, t1.name hometeam, t2.name  awayteam
from games g 
inner join teams t1 on g.hometeamid = t1.id
INNER JOIN teams t2 on g.hometeamid = t2.id