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
3
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演示
Note that i'm escaping `match`
as it is a reserved word
注意,我要转义“match”,因为它是一个保留词
#2
0
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
3
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演示
Note that i'm escaping `match`
as it is a reserved word
注意,我要转义“match”,因为它是一个保留词
#2
0
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(第一个语法)更好,它更容易读,并且当表的数量随着左/右连接的增加而增加时,它可以处理一些难看的事情。