连接表mysql -没有双重迭代

Let me start by saying this should be a relatively simple problem which is / was made unnecessary complicated by bad Database design (not by me) that said im also no expert in mysql.

让我先说，这应该是一个相对简单的问题，由于糟糕的数据库设计(而不是我)造成的不必要的复杂性，我也没有mysql专家。

Consider the following

考虑以下

Table Schedule

表安排

Note how the columns homeID and visitorID contains the names of the teams and not the actual teamID's

请注意，列“homeID”和visitorID是如何包含团队名称的，而不是实际的teamID。

In a bid to fix this I created a new table with columns containing teamID AND teamName as can be seen by below image.

为了解决这个问题，我创建了一个包含teamID和teamName的新表，如下图所示。

Table Teams

表队

My Problem(s)

我的问题(s)

I must get the teamID from table Teams for BOTH home team AND away team

无论是主队还是客场，我都必须得到客场的比赛

So I created the Teams table and this simple script:

所以我创建了团队表和这个简单的脚本:

SELECT schedule.*, teams.* FROM schedule JOIN teams ON schedule.homeID = teams.teamName OR schedule.visitorID = teams.teamName WHERE schedule.gameID = 411 LIMIT 1 #added Limit1 else the code generates to rows

选择安排。*,团队。*按期加入团队。homeID =团队。名为teamName或时间表。visitorID =团队。名为teamName时间表。gameID = 411限制1 #添加的限制，否则代码将生成行。

Output of mysql Script

mysql脚本的输出

Limit 1

限制1

Notice above how teamID is only generated for 1 team with Limit 1

请注意，teamID是如何仅为一个具有限制1的团队生成的。

No Limit Statement (Double Iteration)

无极限语句(双迭代)

Notice above how teamID can get retrieved for BOTH teams. Problem is its doing a double iteration.

请注意上面的提示，如何为两个团队检索teamID。问题是它进行了双重迭代。

TLDR; The above presents the following problems

TLDR;以上所提出的问题如下

Firstly the script will generate two outputs one for home team and once for away team. As to be expected however I cant have that.

首先，脚本将生成两个输出，一个用于主队，一个用于客场。然而，我却不能接受。
As a workaround to Problem number 1 -- I added Limit 1 the problem I get with Limit though is that it only gives back a single teamID (as to be expected, I guess)

作为第一个问题的解决方案——我添加了第一个极限——我得到的问题是它只返回一个teamID(我猜应该是意料之中的)

Question

问题

How can I get BOTH teamID's from table teams with a single iteration? Hope this make sense....

如何从单次迭代的表团队中获得两个teamID ?希望这是有意义的....

Extra

额外的

A demo of application with hard coded team names looks like this (just to give an idea of what they are trying to achieve)

一个带有硬编码的团队名称的应用程序的演示看起来是这样的(只是为了说明他们想要实现的目标)

3 个解决方案

#1

Sounds like you want to join teams twice to schedule.

听起来你想要两次加入团队。

SELECT s.*,
       th.*,
       ta.*
       FROM schedule s
            INNER JOIN teams th
                       ON s.homeid = th.teamname
            INNER JOIN teams ta
                       ON s.visitorid = ta.teamname 
       WHERE s.gameid = 411;

#2

I guess that you want to show both team in one row instead of two rows.
If yes, then you need to join the table teams twice.

Consider this demo: http://www.sqlfiddle.com/#!9/bb5e61/1

This join will collect both teams into one row:

我猜你想把两个队都显示在一行而不是两行。如果是，那么您需要加入表团队两次。考虑这个演示:http://www.sqlfiddle.com/ # !9/bb5e61/1这个加入将把两个团队集合在一起:

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
JOIN Teams t1 ON s.homeId = t1.teamName
JOIN Teams t2 ON s.visitorId = t2.teamName;

| id | homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|--------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 | Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |

However you can also consider LEFT joins instead on INNER joins, which will work in a case where there is no relevant data in the TEAMS table:

但是，您也可以考虑在内部连接上使用左连接，这在团队表中没有相关数据的情况下是可行的:

SELECT s.*,
       t1.teamId as homeId_teamId,
       t1.teamCode as homeId_teamCode,
       t1.teamName as homeId_teamName,
       t2.teamId as visitorId_teamId,
       t2.teamCode as visitorId_teamCode,
       t2.teamName as visitorId_teamName      
FROM Schedule s
LEFT JOIN Teams t1 ON s.homeId = t1.teamName
LEFT JOIN Teams t2 ON s.visitorId = t2.teamName;

| id |   homeId | visitorId | homeId_teamId | homeId_teamCode | homeId_teamName | visitorId_teamId | visitorId_teamCode | visitorId_teamName |
|----|----------|-----------|---------------|-----------------|-----------------|------------------|--------------------|--------------------|
|  1 |   Poland |  Colombia |             1 |              PL |          Poland |                2 |                 CO |           Colombia |
|  3 | Ya Majka |    Poland |        (null) |          (null) |          (null) |                1 |                 PL |             Poland |
|  2 | Ya Majka |   Rossija |        (null) |          (null) |          (null) |           (null) |             (null) |             (null) |

Here are the scripts that make up the tables from the examples

下面是用示例组成表格的脚本

CREATE TABLE Schedule(
  id int,   homeId varchar(20),visitorId varchar(20)
);

INSERT INTO Schedule VALUES
(1, 'Poland', 'Colombia' ),(2,'Ya Majka','Rossija'), 
(3,'Ya Majka','Poland');

CREATE TABLE Teams( 
  teamId int, teamCode varchar(10), teamName varchar(20)
);

INSERT INTO Teams VALUES
(1, 'PL', 'Poland' ),(2,'CO','Colombia'),(3,'US','United States');

#3

You can use a subquery (two of them in the same query) to solve this:

您可以使用子查询(其中两个在同一个查询中)来解决这个问题:

select
  gameID, 
  weekNum,
  gameTimeEastern,
  (select teamName from teams where teamID = schedule.homeID) as homeName,
  homeScore,
  (select teamName from teams where teamID = schedule.visitorID) as visitorName,
  visitorScore from schedule;

This doesn't get all the columns from schedule, just an example to show how it works. If you need various queries (including select *, though this isn't a good practice except for testing), you could create a view based on a query like the above (with ALL columns from schedule, except homeID and visitorID that get replaced with sub-queries from the teams table). Then you can place queries against that view - and they will work like the original table where you had team names directly in it.

这并不是从schedule中获得所有的列，只是一个示例来展示它是如何工作的。如果您需要各种查询(包括select *，尽管除了测试之外这不是一个好的实践)，您可以基于如上所示的查询创建一个视图(除了homeID和visitorID之外，所有列都来自schedule)。然后，您可以针对该视图放置查询——它们将像原始表一样工作，其中直接包含团队名称。

#1