MySQL - Join Issues

Question

Consider I'm having 4 tables in my Database : Team, Player, Training and Team_player

A team can have multiple players. A player has only one team.

A player can have multiple trainings.

I want to get all the player informations, with the date or their next training. Next training is next date that is after current date. Some players don't have a next training date but I still want their informations.

Here are the tables :

--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 |

Here is what I want :

| 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 |

I tried different things, LEFT JOINS, conditions... I made some search but I didn't find an answer for my case. Here is an example :

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

But this is not what I want because if a player doesn't have a next training, I don't get any information about him.

Answer 1

You have the LEFT JOIN backwards, the player table needs to be on the LEFT side:

    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 player.id_player = training.id_player
    WHERE training.start_date > CURRENT_DATE
    GROUP BY player.id_player

This answer is only dealing with the specific issue you indicated:

But this is not what I want because if a player doesn't have a next training, I don't get any information about him.

Others have good suggestions regarding other possible issues.

Answer 2

You should add the condition for left joined table to the related ON clause

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

if you add the condition related to left join tables in where clause this work as an inner join

If you want avoid duplicated rows and not are using aggreagtion function youn should use distinct clause