Get id details from another table in MYSQL-CodePudding

I'm trying to get the value of id from another table

I have a table world_match :

and teams_world:

I'm trying to get the id, date, the name of the home team and away team :

Expected:

id: 1
Date: 25/12/2022
Home: Qatar
Away: Ecuador

So currently, I have a problem with my sql:

SELECT id_match, date_debut, id_domicile, id_exterieur FROM match_world m INNER JOIN teams_world t ON m.id_domicile = t.id_equipe AND m.id_exterieur = t.id_equipe

Someone can explain me my problem in this sql request please ?

CodePudding user response：

I can see what you want to accomplish, but you're going about it the wrong way. You need to join the match_world twice with the teams_world table, once for the home team and once of the away team.

SELECT 
  match_world.id_match, 
  match_world.date_debut, 
  team_home.nom AS home_nom, 
  team_away.nom AS away_nom
FROM 
  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

You were quite close. Note also that I like to write my queries in a way that they are easy to read, on several line, with indentations, and no abbreviations. This does not impact performance.

(query is untested, no guarantee of functionality given...)