SQL:如何从另一个表中的表中选择不同的值?

I have two tables

我有两个表

Table 'Teams' has two columns

表“团队”有两列。

teamID,
teamName

Table 'Match' has three columns

表“Match”有三列

matchID, 
teamID_1,
teamID_2

... like in image bellow:

…像在图像波纹管:

How would I construct a select statement which will pull through the Teams.teamName to both the Match.teamID_1 and the Match.teamID_2 based on their respective IDs?

我将如何构造一个select语句，该语句将通过这些团队。两场比赛的队名。teamID_1和匹配。teamID_2基于各自的id ?

I can do that only with one column or another, but not both:

我只能用一列或另一列，但不能同时用这两列:

SELECT Match.matchID, Teams.teamName
    FROM Match
    INNER JOIN Teams ON Match.teamID_1 = Teams.teamID

OR

或

SELECT Match.matchID, Teams.teamName
    FROM Match
    INNER JOIN Teams ON Match.teamID_2 = Teams.teamID

1 个解决方案

#1

You can join the same table multiple times. Just alias them (t1 and t2 as shown below):

您可以多次连接同一个表。只对它们进行别名(t1和t2，如下所示):

SELECT 
    Match.matchID, 
    t1.teamName, 
    t2.teamName
FROM Match
    INNER JOIN Teams t1 ON Match.teamID_1 = t1.teamID
    INNER JOIN Teams t2 ON Match.teamID_2 = t2.teamID