MySQL - 连接问题

Question

假设我的数据库中有 4 个表:球队、球员、训练和 Team_player

一个团队可以有多名玩家。一名玩家只能拥有一支球队。

一个玩家可以进行多次训练。

我想获取所有球员信息，包括日期或他们的下一次训练。下一次训练是当前日期之后的下一个日期。有些球员没有下次训练日期，但我仍然想要他们的信息。

以下是表格:

--Team--

| id_team | name |
|----------|------|
| 1        | Tata |
| 2        | Fifi |
| 3        | Fofo |

--Player--

| id_player | name | age |
|------------|------|-----|
| 1          | Jon  | 32  |
| 2          | Jim  | 21  |
| 3          | Andy | 20  |

-- Team_Player--

| id_team | id_player |
|----------|------------|
| 1        | 1          |
| 2        | 2          |
| 3        | 3          |

-- Training --

| id_training | id_player | start_date  |
|--------------|------------|--------------|
| 1            | 1          | 2020-01-12 |
| 2            | 1          | 2019-12-25 |
| 3            | 2          | 2014-05-12 |
| 4            | 3          | 2020-05-03 |

这就是我想要的:

| id_team | id_player | id_training | player.name | age | start_date  |
|----------|------------|--------------|--------------|-----|--------------|
| 1        | 1          | 2            | Jon          | 32  | 2019-12-25 |
| 2        | 2          | NULL         | Jim          | 21  | NULL         |
| 3        | 3          | 4            | Andy         | 20  | 2020-05-03 |

我尝试了不同的东西，左连接，条件...我做了一些搜索，但没有找到适合我的情况的答案。这是一个例子:

SELECT training.start_date, player.name, training.id_training, team.id_team, player.age, player.id_player
FROM player
INNER JOIN player_team ON player_team.id_player = player.id_player
INNER JOIN team ON team.id_team = player_team.id_team
LEFT JOIN training ON training.id_player = player.id_player
WHERE training.start_date > CURRENT_DATE
GROUP BY player.id_player

但这不是我想要的，因为如果一名球员没有进行下一次训练，我就无法获得有关他的任何信息。

Answer 1

您应该将左连接表的条件添加到相关的 ON 子句

SELECT training.start_date
  , player.name
  , training.id_training
  , team.id_team
  , player.age
  , player.id_player
FROM player
INNER JOIN player_team ON player_team.id_player = player.id_player
INNER JOIN team ON team.id_team = player_team.id_team
LEFT JOIN training ON training.id_player = player.id_player
  AND  training.start_date > CURRENT_DATE

如果您在 where 子句中添加与左联接表相关的条件，则这将作为内联接

如果你想避免重复的行并且不使用聚合函数，你应该使用distinct子句