Get specific values from some tables in SQL-CodePudding

I have three tables:

teams_world:

match_world:

resultat_world:

So I want to display the resultat of the match with specific details:

Currently I have this:

SELECT 
    resultat_world.id_match,
    team.nom AS team_winner, 
    resultat_world.score_home,
    resultat_world.score_away
  FROM 
    resultat_world 
  INNER JOIN 
  teams_world AS team 
    ON resultat_world.id_equipe_winner = team.id_equipe

So now, What I want, it's to display the "id_domicile" and "id_exterieur" :

Expected: id_match - home_nom - away_nom - team_nom - score_home - score_away

My try :

SELECT 
    resultat_world.id_match,
    team_home.nom AS home_nom, 
    team_away.nom AS away_nom,
    team.nom AS team_winner, 
    resultat_world.score_home,
    resultat_world.score_away
  FROM 
    resultat_world,match_world
  INNER JOIN 
    teams_world AS team_home 
    ON match_world.id_domicile = team_home.id_equipe 
  INNER JOIN 
    teams_world AS team_away 
    ON match_world.id_exterieur = team_away.id_equipe
  INNER JOIN 
  teams_world AS team 
    ON resultat_world.id_equipe_winner = team.id_equipe

Someone can explain me where is my problem ? Thank you

CodePudding user response：

You're probably getting an #1054 - Unknown column 'resultat_world.id_equipe_winner' in 'on clause' error.

This is because you're using a comma (implicit cross join) in your FROM clause, which has a lower precedence than the INNER JOINs. The INNER JOINs are then calculated before the comma in the FROM clause, so when the INNER JOIN to the teams_world AS team table is calculated, the resultat_world.id_equipe_winner column does not exist yet. See: https://stackoverflow.com/a/54738895/3960296

Fix by either adding parentheses to calculate the implicit cross join first:

  FROM 
    (resultat_world,match_world)

Or use the explicit cross join syntax which does the same without the need for parentheses:

  FROM 
    resultat_world CROSS JOIN match_world

Both select all of the rows from the resultat_world table, and then combine each of those rows with all of the rows from the match_world table (cartesian product).

For your desired result you probably want to add a WHERE clause to only keep those rows where the id_match of the resultat_world row matches the id_match of the match_world row:

SELECT 
    resultat_world.id_match,
    team_home.nom AS home_nom, 
    team_away.nom AS away_nom,
    team.nom AS team_winner, 
    resultat_world.score_home,
    resultat_world.score_away
  FROM 
    (resultat_world,match_world)
  INNER JOIN 
    teams_world AS team_home 
    ON match_world.id_domicile = team_home.id_equipe 
  INNER JOIN 
    teams_world AS team_away 
    ON match_world.id_exterieur = team_away.id_equipe
  INNER JOIN 
  teams_world AS team 
    ON resultat_world.id_equipe_winner = team.id_equipe
    
WHERE resultat_world.id_match = match_world.id_match

You can get the same result with an INNER JOIN, this is the recommended approach (do not use cross joins):

SELECT 
    resultat_world.id_match,
    team_home.nom AS home_nom,
    team_away.nom AS away_nom,
    team.nom AS team_winner,
    resultat_world.score_home,
    resultat_world.score_away

FROM 
    resultat_world
INNER JOIN
    match_world
    ON resultat_world.id_match = match_world.id_match

INNER JOIN
    teams_world AS team_home 
    ON match_world.id_domicile = team_home.id_equipe
INNER JOIN
    teams_world AS team_away 
    ON match_world.id_exterieur = team_away.id_equipe
INNER JOIN
    teams_world team
    ON resultat_world.id_equipe_winner = team.id_equipe

Hope this helps!