这个SQL语句怎么写?(菜鸟提问,大家拿分)

[popo1]

两个表: Teams(球队表) Matchs(比赛表)

Teams(表结构):
team_id int identity(1,1), (主键)
team_name nvarchar(20) not null
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Matchs(表结构):
match_id int identity(1,1),
mteam_id int not null, --主队id (外键)
gteam_id int not null --客队id (外键)

_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Matchs表中的mteam_id 和 gteam_id 都是 Teams表中的 team_id的外键

问题是:
怎样创建一个存储过程: sp_GetMatchDetails (获取所有比赛的信息,包括球队名称等详细资料)--包括 match_id,mteam_id,team_name(主队的名称--引用Teams表),gteam_id,team_name(客队的名称--引用Teams表)
--就是在获取mteam_id和gteam_id的球队名称(相应的team_name)时遇到困难

大家帮忙,谢谢!

[victorycyz]

select
match_id,a.team_name as '主队',b.team_name as '客队'
from
matchs c join teams a on c.mteam_id = a.team_id
join teams b on c.gteam_id = b.team_id

[j9988]

select match_id,mteam_id,team_name,gteam_id,team_name
from Matchs A
join Teams B on A.team_id=B.mteam_id
join Teams C on A.team_id=C.gteam_id

[j9988]

错了 :(

[j9988]

select match_id,mteam_id,team_name,gteam_id,team_name
from Matchs A
join Teams B on B.team_id=A.mteam_id
join Teams C on C.team_id=A.gteam_id

[klan]

select match_id,mteam_id,A.team_name,gteam_id,B.team_name from matchs,Teams A, Teams B where Matchs.mteam_id=A.team_id and Matchs.gteam_id=B.team.id