CODEWITHSUNDEEP
mysql
Robin Alexander
Robin Alexander

Reputation: 1004

Get two names by ID from another table

For a hockey website, I would like to display past results from the home team and of course display the name of the away team. I could make it almost to the end, but I can't the away team name to show up. I receive all needed results from the RESULTS table. In there I have the home_team_id and the away_team_id, which I need to connect to the TEAM table in order to show the team name. I could do this with the home team but have no idea how to get the away team names of those found in there.

results table

results

teams table

teams

This is what shows me everything, but no away team name:

SELECT results.home_team_id
     , results.away_team_id
     , results.ft_score
     , teams.name 
  FROM results 
  LEFT 
  JOIN teams 
    ON results.home_team_id = teams.team_id 
 WHERE results.home_team_id = '$hteam‘"

I have check other post with similar problems/question, but they did not help me. Sorry.

Help would be great! Thank you very much!

Upvotes: 1

Views: 35

Answers (1)

juergen d
juergen d

Reputation: 204746

You need to join the teams table twice with different alias names

SELECT results.home_team_id, results.away_team_id, 
       results.ft_score, 
       thome.name as home_name, taway.name as away_name 
FROM results 
LEFT JOIN teams thome ON results.home_team_id = thome.team_id 
LEFT JOIN teams taway ON results.away_team_id = taway.team_id 
WHERE results.home_team_id = '$hteam'

Upvotes: 3

Related Questions