SQL - 将 2 个外键左连接到 1 个主键 [英] SQL - Left join 2 foreign keys to 1 primary key

问题描述

我有两张桌子，游戏和团队.我的 sql 语句应该是什么样子来制作一个游戏列表，该列表将链接到 TeamID1 和 TeamID2 字段的 TeamName 拉进来?我相信我可以使用左连接，但我不确定如何处理链接到一个主键的两个外键.非常感谢您提供的任何帮助.

I have two tables, Games and Teams. What should my sql statement look like to make a list of games that pulls in the TeamName that is linked to the TeamID1 and TeamID2 fields? I believe I could use a left join but I'm not sure what to do with two foreign keys that link to one primary key. Thank you very much for any help you could provide.

游戏ID
团队 ID1
团队 ID2
结果

GameID
TeamID1
TeamID2
Result

团队ID
团队名称

TeamID
TeamName

推荐答案

我经常看到人们为在同一查询中单独或多次加入表的想法而苦恼(就像这里一样).一旦掌握，这是一种很好的技术，可用于在行之间有很多关系的表格(例如必须互相比赛的团队列表！).正如其他人指出的那样，您需要使用两个 inner join 来完成此操作:

I often see folks struggle with the idea of joining a table unto itself or multiple times in the same query (as it were here). Once mastered, it's a great technique to use on tables that have a lot of relationships between rows (such as a list of teams that have to play each other!). As others have pointed out, you need to use two inner joins to accomplish this:

select
    *
from
    games g
    inner join teams t1 on
        g.teamid1 = t1.teamid
    inner join teams t2 on
        g.teamid2 = t2.teamid

因此，如果您的 games 表如下所示:

So, if your games table looks like this:

GameID    TeamID1    TeamID2
----------------------------
     1          1          3
     2          4          2
     3          2          1

您将获得以下结果集:

g.GameID    g.TeamID1    g.TeamID2    t1.TeamID    t1.Name    t2.TeamID    t2.Name
----------------------------------------------------------------------------------
       1            1            3            1    Lions              3    Bears
       2            4            2            4    Oh My              2    Tigers
       3            2            1            2    Tigers             1    Lions

当然，如果我是我，为了可用性，我会在 select 语句中为这些列添加别名:

Of course, I would alias these columns in the select statement, if I were me, for usability's sake:

select
    g.GameID,
    t1.Name as Team1,
    t2.Name as Team2
from
    ...

这样，可以适当地命名列，而不是让 t1 和 t2 列共享相同的名称.

This way, columns can be named appropriately, instead of having the t1 and t2 columns share the same names.

现在，解决关于什么是 left join 的混淆.您会看到，left join 将获取第一个(或左侧)表中的所有行，然后将连接条件上的任何行与第二个(或右侧)表进行匹配.对于左表中的任何行，您将在 右 表的所有列中获得 null.

Now, to address the confusion about what a left join is. You see, a left join will take all of the rows from the first (or left) table, and then match up any rows on the join condition to the second (or right) table. For any rows from the left table, you will get null in all of the columns on the right table.

深入研究一个示例，假设有人出于某种原因在其中一行中为 TeamID2 输入了 null.假设曾经存在一个 TeamID 4 的团队，但现在不存在了.

Delving into an example, let's say that somebody put in a null for TeamID2 on one of the rows for whatever reason. Let's also say that a team of TeamID 4 used to exist, but doesn't any more.

GameID    TeamID1    TeamID2
----------------------------
     1          1          3
     2          4          2
     3          1       null

现在，让我们来看看 left join 在查询方面是什么:

Now, let's take a look at what a left join would be in terms of the query:

select
    *
from
    games g
    left join teams t1 on
        g.teamid1 = t1.teamid
    left join teams t2 on
        g.teamid2 = t2.teamid

逻辑上，这将抓取我们所有的游戏，然后将它们与各自的团队匹配.但是，如果 TeamID 不存在，我们将得到 nulls.它看起来像这样:

Logically, this will grab all of our games, and then match them up to the respective teams. However, if a TeamID doesn't exist, we'll get nulls. It will look like so:

g.GameID    g.TeamID1    g.TeamID2    t1.TeamID    t1.Name    t2.TeamID    t2.Name
----------------------------------------------------------------------------------
       1            1            3            1    Lions              3    Bears
       2            4            2         null    null               2    Tigers
       3            1         null            1    Lions           null    null

因此，如果团队是可选的，left join 将仅是必要的.

Therefore, a left join will only be necessary if a team is optional.

在您的情况下，您将使用 inner join 多次连接表.这是一种非常普遍的做法，而且相当有用.它避免了子查询的一些陷阱(尤其是在 MySQL 上)，同时允许您从表中获取数据以进行内部比较.这在尝试查找某事或相关行的顺序时非常有用.

In your case, you'll be using an inner join to join a table multiple times. This is a very common practice and is rather useful. It avoids some of the pitfalls of subqueries (especially on MySQL), while allowing you to grab data from the table for intratable comparisons. This is markedly useful when trying to find the order of something, or related rows.

无论如何，我希望这个非常漫不经心的答案能帮助到某个地方的人.

Anyway, I hope this very rambling answer helps out somebody somewhere.

这篇关于SQL - 将 2 个外键左连接到 1 个主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！