我有两个这样的表：
# Match
id, team1_id , team2_id
-----------------------
1, 10, 20
2, 10, 50
# Team
id, team_name, team_country
---------------------------
10, team A , England
20, team B , France
我正在尝试从包含两支球队信息的比赛表中获取列表，我想要一些类似的东西：
Team A (England) vs Team B (France)
我试过这个，但我得到了错误的团队信息，我的查询肯定有问题。
这是我的查询：
SELECT `match`.*,
`t1`.`team_country` as team1_country,
`t2`.`team_country` as team2_country
FROM `match`,
`team` t1 , `team` t2
WHERE `match`.`team1_id` = `t1`.`id` and `match`.`team2_id` = `t2`.`id`
提前感谢！
我只是在我的测试机器上用 postgres 摆弄它。SQL 不应该有所不同：
lara=# create table match ( id serial primary key, team1 int, team2 int);
CREATE TABLE
lara=# create table teams ( id serial primary key, name text, country text);
CREATE TABLE
lara=# insert into match(id, team1, team2) values (1,1,2),(2,1,3),(3,2,1);
INSERT 0 3
lara=# select * from match;
id | team1 | team2
----+-------+-------
1 | 1 | 2
2 | 1 | 3
3 | 2 | 1
(3 rows)
lara=# insert into teams values (1, 't1', 'en');
INSERT 0 1
lara=# insert into teams values (2, 't2', 'de');
INSERT 0 1
lara=# insert into teams values (3, 't3', 'fr');
INSERT 0 1
lara=# select * from match m left join teams t1 on t1.id=m.team1 left join teams t2 on t2.id=m.team2;
id | team1 | team2 | id | name | country | id | name | country
----+-------+-------+----+------+---------+----+------+---------
1 | 1 | 2 | 1 | t1 | en | 2 | t2 | de
2 | 1 | 3 | 1 | t1 | en | 3 | t3 | fr
3 | 2 | 1 | 2 | t2 | de | 1 | t1 | en
因此，您的实际查询是正确的。更干净的是：
SELECT * FROM match m
LEFT JOIN teams t1 ON t1.id=m.team1
LEFT JOIN teams t2 ON t2.id=m.team2;
但是你的问题显然不是SQL。