CODEWITHSUNDEEP
mysqlsql
Daniel Underwood
Daniel Underwood

Reputation: 391

Multiple IDs returning values in another table - mysql, php

I have two tables, one called Teams and the other called Scores

Teams

id - team_name  
1  - Reds  
2  - Blues  
3  - Greens  
4  - Yellows

Scores

home_team_id - away_team_id  
 1           -  2  
 3           -  4

I'm looking to get the team names returned in my php file by comparing the IDs for both home and away teams and returning the team name for them from Teams.

I've used:

SELECT * from scores, teams
WHERE scores.home_team_id=teams.id OR scores.away_team_id=teams.id

and also a LEFT JOIN that brings back similar values, but these only return duplicates.

Is anyone able to assist so that the IDs shown for both home and away teams are reflected as per the team_name in Teams.

Upvotes: 0

Views: 43

Answers (1)

Fabricator
Fabricator

Reputation: 12772

You need to join scores with teams one time for home_team, and join another time for the away_team:

select b.team_name home, c.team_name away
from scores a
join teams b on a.home_team_id = b.id
join teams c on a.away_team_id = c.id

Upvotes: 2

Related Questions