Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Need some help on mysql inner join

I have two tables, named matches and teams.

Matches:
tournament_id, match_id, score, home_team_id and away_team_id
enter image description here

Teams: team_id, team_name
enter image description here

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

My SQL query must result in the filled in team_name for the home_team_id as also the away_team_id
I know that I will need INNER JOIN to get the result, but the only good result I get is the home_team_id withe the team_name or the away_team_id with the team_name but not both. See code below

SELECT  matches.tournament_id,
    matches.match_id,
    matches.score,
    matches.home_team_id, 
    matches.away_team_id,
    teams.team_id,
    teams.team_name
FROM matches
INNER JOIN teams ON matches.home_team_id = teams.team_id
WHERE matches.tournament_id = 'WC-1930'


How can I get the result of both the team_name for the home_team_id and the away_team_id?

>Solution :

Does this help achieve what you want- adding a second link to teams based on the away team. EDIT: It was pointed out that my change ended up with some duplicate field names so have fixed this as well.

SELECT  matches.tournament_id,
    matches.match_id,
    matches.score,
    matches.home_team_id, 
    matches.away_team_id,
    h.team_id as home_id,
    h.team_name as home_name,
    a.team_id as away_id,
    a.team_name as away_name
FROM matches
INNER JOIN teams h ON matches.home_team_id = h.team_id
INNER JOIN teams a ON matches.away_team_id = a.team_id
WHERE matches.tournament_id = 'WC-1930'
ORDER BY matches.match_id ASC;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading