我有篮球比赛的桌子和一支像这样的篮球队:
MATCHES:
ID | HOME_TEAM_ID | AWAY_TEAM_ID | SCORE_HOME | SCORE_AWAY
----------------------------------------------------------------
1 | 20 | 21 | 80 | 110
2 | 12 | 10 | 96 | 90
TEAMS:
ID | NAME
-------------------------
20 | BULLS
21 | KNICKS
给出了一个匹配ID,我想检索得分和团队名称。我该如何加入从团队表中检索两个团队名称?
我尝试过:
SELECT *
FROM matches AS m
JOIN teams AS t1 ON t.id = m.home_team_id
JOIN teams AS t2 ON ti.id = m.away_team_id
WHERE m.id = 1
...但是,这里的第二个加入语句的结果似乎覆盖了第一个的结果,所以我只得到一个名称:
[id] => 1
[score_home] => 80
[score_away] => 110
[name] => KNICKS
我也尝试了:
SELECT *
FROM matches AS m
JOIN teams AS t ON (t.id = m.home_team_id OR t.id = m.away_team_id)
WHERE m.id = 1
...返回两个结果:
[id] => 1
[score_home] => 80
[score_away] => 110
[name] => BULLS
和
[id] => 1
[score_home] => 80
[score_away] => 110
[name] => KNICKS
我想进行一个查询,以返回类似的东西
[id] => 1
[score_home] => 80
[score_away] => 110
[name_home_team] => BULLS
[name_home_team] => KNICKS
可能吗?
SELECT
Matches.ID,
Matches.Score_Home,
Matches.Score_Away,
HomeTeam.Name Home_Team_Name,
AwayTeam.Name Away_Team_Name
FROM
Matches
INNER JOIN Teams HomeTeam ON Matches.Home_Team_ID = HomeTeam.ID
INNER JOIN Teams AwayTeam ON Matches.Away_Team_ID = AwayTeam.ID
您只需要名称列名
SELECT m.ID,
m.SCORE_HOME,
m.SCORE_AWAY,
t1.NAME as name_home_team,
t2.NAME as name_home_team
FROM MATCHES AS m
JOIN teams AS t1 ON t1.id = m.home_team_id
JOIN teams AS t2 ON t2.id = m.away_team_id
WHERE m.id = 1
select m.ID,
(select NAME from TEAM where id=m.HOME_TEAM_ID) HOME_TEAM_NAME,
m.SCORE_HOME,
(select NAME from TEAM where id=m.AWAY_TEAM_ID) AWAY_TEAM_NAME,
m.SCORE_AWAY
from
MATCHES m
where m.ID=1
未测试,应起作用:
select x.id, x.score_home, x.score_away, y.name as home_team, z.name as away_team
from matches x,
(select name from teams where id = x.home_team_id)y,
(select name from teams where id = x.away_team_id)z
where x.id = 1;