Sql query about joining 2 select-CodePudding

I would like to know how can i join 2 select statements that are like this:

Select Team.name from Team join Match on Team.id == Match.teamHomeid
Select Team.name from Team join Match on Team.id == Match.teamAwayid

The error i get is cause i dont have separate IDs for Home and Away teams

Thank you in advance

CodePudding user response：

SELECT(SELECT Team.name
       FROM Team
                JOIN Match ON Team.id == Match.teamHomeid) AS teamHome,
      (SELECT Team.name
       FROM Team
                JOIN MATCH ON Team.id == MATCH.teamAwayid) AS teamAway;

CodePudding user response：

You can use the same table multiple times. ie:

Select t1.Name as HomeTeam, t2.Name as AwayTeam 
from Match m
inner join Team t1 on t1.Id = m.teamHomeId
inner join Team t2 on t2.Id = m.teamAwayId