Daniel Underwood Daniel Underwood - 1 year ago 70
SQL Question

Multiple IDs returning values in another table - mysql, php

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


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


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.

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download