INNER JOIN twice form the same table

I have 2 database tables that I want to JOIN.

DB table 1 had football fixtures in it. So Team A plays Team B and the score was. The data comes from an API and in this DB table the teams are numbers instead of actual names.

In the second DB table I have a list of all the teams names with their ID. Those ID’s are give in DB table 1.

I’ve managed to INNER JOIN the home teams, but I can’t figure out how I can JOIN the away teams as well.

This is what I have so far. I don’t manage to get the visitors teams matched via INNER JOIN.

    <table border="1">
<tr>
<th>Home Team</th>
<th>Away Team</th>
<th>FT Score</th>
</tr>
<?php
global $wpdb;

    $sql = "SELECT wp_football_table_version.localteam_id, wp_AllTeams_table_version.team_name, wp_football_table_version.ft_score, wp_football_table_version.visitorteam_id
    FROM wp_football_table_version
    INNER JOIN wp_AllTeams_table_version ON wp_football_table_version.localteam_id=wp_AllTeams_table_version.team_id 
    INNER JOIN wp_AllTeams_table_version ON wp_football_table_version.visitorteam_id=wp_AllTeams_table_version.team_id 
    ";
    $result = $wpdb->get_results($sql);

    foreach ($result as $print) {
?>
<tr>
<td><?php echo $print->team_name ;?></td>
<td><?php echo $print->visitorteam_id;?></td>
<td><?php echo $print->ft_score ;?></td>

</tr>
<?php
}
?>
</table>

Answer

Since you are going to use the same table again you need to use alias

$sql = "SELECT wp_football_table_version.localteam_id, home.team_name, visitor.team_name, wp_football_table_version.ft_score, wp_football_table_version.visitorteam_id
    FROM wp_football_table_version
    INNER JOIN wp_AllTeams_table_version as home ON wp_football_table_version.localteam_id=home.team_id 
    INNER JOIN wp_AllTeams_table_version as visitor ON wp_football_table_version.visitorteam_id=visitor.team_id