user3384194 user3384194 - 4 months ago 6
MySQL Question

SQL two rows in one

My sql database looks like this below:

Table Match:

id | homeTeamId | guestTeamId
------ | ---------- | ----------
1 | 1 | 2
------ | ---------- | ----------
2 | 13 | 9


Table Team:

id | Name
------ | ------
1 | Arsenal London
------ | ------
2 | Manchester City
------ | ------
9 | Tottenham
------ | ------
13 | Aston Villa


Now I want to make a query, which return all matches with my teamnames.
My output should look like this:

id | homeTeamId | guestTeamId | hometeam | guestteam
------ | ---------- | ----------- | ---------------| ---------
1 | 1 | 2 | Arsenal London | Manchester City
------ | ---------- | ----------- | ---------------| ---------
2 | 13 | 9 | Aston Villa | Tottenham


So, how can I do this? I tried a lot with inner joins, left joins, group by,.... But the problem is, that I need two joins on one table and give an alias for the same column.

Thanks for your help!

Answer

There are several ways to do this. Here's one using multiple joins:

select m.id, m.hometeamid, m.guestteamid, t1.name hometeam, t2.name guestteam
from match m 
    join team t1 on m.hometeamid = t1.id
    join team t2 on m.guestteamid = t2.id
Comments