mysql问题: populate table from another table but referencing ID instead of name

Question

我现在正尝试用“摘要”表中的数据填充“testMatch”表(如下):

测试匹配表

+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| match_id         | int(11)      | NO   | PRI | NULL    |       | 
| match_date       | date         | YES  |     | NULL    |       | 
| ground           | varchar(50)  | YES  | MUL | NULL    |       | 
| homeTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| awayTeam         | varchar(100) | YES  | MUL | NULL    |       | 
| matchResult      | varchar(100) | YES  | MUL | NULL    |       | 
| manOfMatch       | varchar(30)  | YES  |     | NULL    |       | 
| homeTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| homeTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_captain | int(10)      | YES  | MUL | NULL    |       | 
| awayTeam_keeper  | int(10)      | YES  | MUL | NULL    |       | 
+------------------+--------------+------+-----+---------+-------+

填充 match_id -----> manOfMatch 没有问题 - 我在导入时遇到问题的是“homeTeam_captain”、“homeTeam_keeper”、“awayTeam_captain”和“awayTeam_keeper”。

汇总表

mysql> DESCRIBE SUMMARY;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| matchID         | int(11)      | NO   | PRI | NULL    |       | 
| Test            | int(11)      | YES  |     | NULL    |       | 
| matchDate       | date         | YES  |     | NULL    |       | 
| Ground          | varchar(50)  | YES  |     | NULL    |       | 
| HomeTeam        | varchar(100) | YES  |     | NULL    |       | 
| AwayTeam        | varchar(100) | YES  |     | NULL    |       | 
| matchResult     | varchar(50)  | YES  |     | NULL    |       | 
| MarginRuns      | int(11)      | YES  |     | NULL    |       | 
| MarginWickets   | int(11)      | YES  |     | NULL    |       | 
| ManOfMatch      | varchar(40)  | YES  |     | NULL    |       | 
| HomeTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| HomeTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamCaptain | varchar(30)  | YES  |     | NULL    |       | 
| AwayTeamKeeper  | varchar(30)  | YES  |     | NULL    |       | 
+-----------------+--------------+------+-----+---------+-------+

我需要以某种方式从摘要中选择数据，获取相应的 player_id 并将 player_id 输入到我的“testMatch”中。下面的玩家表:

玩家表

mysql> describe players;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| player_id      | int(11)      | NO   | PRI | NULL    | auto_increment | 
| player_surname | varchar(30)  | YES  |     | NULL    |                | 
| team           | varchar(100) | YES  | MUL | NULL    |                | 
+----------------+--------------+------+-----+---------+----------------+

所以澄清一下，例如。我想从汇总表中选择 homeTeam_captain 数据而不是名称，我想要相应的 player_id。

我假设我需要使用某种连接/子查询来完成这项工作......我已经尝试首先找到正确的选择查询以确保我正在提取正确的数据，并且我一直在使用下面的测试代码(感谢用户 Larry_Croft 帮助我完成这个):

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamcaptain = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

但这正确地带回了 65 行(65 场比赛)，但我随后尝试了:

select matchID, player_id, player_surname, team from players p, summary s
where
s.hometeamKEEPER = p.player_surname AND s.HomeTeam = p.team ORDER BY matchID;

但这只带回了 61 行(应该是 65 行)所以我一定是逻辑上有错误。

一旦我让这个选择工作，我就需要以某种方式将它包含到我的'INSERT INTO.....SELECT 语句中，以将所有数据放入'testMatch'表中。

我希望这是有道理的，感谢您的帮助!

Answer 1

对我来说，似乎有一个 hometeamKEEPER 具有空值，或者 hometeamKEEPER 的值不在玩家表中。

使用以下查询，您应该能够找到不在玩家表中的 hometownKEEPER:

SELECT matchID, player_id, player_surname, team 
FROM players p 
     RIGHT JOIN summary s ON p.hometeamKEEPER = p.player_surname AND 
                             s.HomeTeam = p.team
ORDER BY matchID;