inner join selecting multiple id as name

I am really stuck, i have 2 tables with football match data:

team

id, name

fixture

id, hometeam, awayteam

I have done the following:

SELECT hometeam, awayteam, score, week FROM fixture
join team on fixture.hometeam = team.id;

basically the hometeam and awayteam are id numbers that relate to the team table, but i n开发者_高级运维eed to output these as the actual text name.

any help would be grately appreciated

thanks :)

SELECT  th.name AS hometeam_name, ta.name AS awayteam_name, week, score
FROM    fixture
JOIN    team th
ON      th.id = fixture.hometeam
JOIN    team ta
ON      ta.id = fixture.awayteam