将两个SELECT语句连接到一个表中

I am trying to combine the output of two separate SQL statements into one table. I have a list of team names with associated id's in one database and another database with a list of matches in another. e.g

我正在尝试将两个单独的SQL语句的输出合并到一个表中。我在一个数据库中有一个具有关联id的组名列表，在另一个数据库中有一个匹配列表。如

Teams    
-----------------                            
id   name              
1    Arsenal                    
2    Aston Villa

Matches
--------------------------------
id   home_team_id   away_team_id
1    1                2

In the matches database, home_team_id and away_team_id are foreign keys of the id in the teams database. What I am trying to do is output the match id with the team names associated with the id's in the matches database. Expected output:

在匹配数据库中，home_team_id和away_team_id是团队数据库中id的外键。我要做的是将匹配id与匹配数据库中与id关联的团队名输出。预期的输出:

match_id        home_team_name      away_team_name
---------------------------------------------------
1               Arsenal             Aston Villa

I have two select statements at the moment.

我现在有两个选择语句。

SELECT match_id, name as home_team_name
FROM matches, teams
WHERE home_team_id = id;

SELECT match_id, name as away_team_name
FROM matches, teams
WHERE away_team_id = id;

The first select statement outputs the match id and the name of the home team, the second outputs the match id and the name of the away team.

第一个select语句输出匹配id和主团队的名称，第二个select语句输出匹配id和客场团队的名称。

Is there any way of achieving the desired output with my current database design?

我目前的数据库设计有什么方法可以达到预期的输出吗?

2 个解决方案

#1

You can do:

你能做什么:

SELECT m.id, t1.name as home_team_name, t2.name as away_team_name
FROM `match` m
INNER JOIN teams t1 ON t1.id = m.home_team_id   
INNER JOIN teams t2 ON t2.id = m.away_team_id

This JOINS the match table with teams table twice, in order to get both team's names.

这将与team表连接两次，以获取两个团队的名称。

sqlfiddle demo

sqlfiddle演示

Note that i'm escaping `match` as it is a reserved word

注意，我要转义“match”，因为它是一个保留词

#2

You can have a table in a from clause multiple times if you define aliases for them (you can only leave one without an alias, so the table name is used as alias).

如果您为它们定义别名，则可以在from子句中多次使用表(您只能保留一个没有别名的表，因此表名用作别名)。

So just do it as:

所以就这样做吧:

select *
from matches
join teams t1 on t1.id = matches.home_team_id
join teams t2 on t2.id = matches.away_team_id

or you can do it as

或者你也可以这样做

select *
from matches, teams t1, teams t2
where t1.id = matches.home_team_id and t2.id = matches.away_team_id

and it is much better to user ANSI JOINs (first syntax), it is more readable, and can handle ugly things when number of tables grows with some left/right joins.

而且，用户ANSI join(第一个语法)更好，它更容易读，并且当表的数量随着左/右连接的增加而增加时，它可以处理一些难看的事情。

#1