SQL Joining 2 tables but 2 columns join to 1 column

Viewed 30

In one table I have team name and team ID. In another table I have Winner and Runner up. I need to link the winner and runner up with the team name from the teams table. I can do it so it either works for winner or runner up but not both. Can anyone help me with it to display both winner and runner up instead of the number. The images below should explain it better than me.

<!DOCTYPE html>
<html>
<head>
<table align="left" width="100%" border="1" style="border-collapse:collapse;">
<tr>
<th><strong>Game Name</strong></th>
<th><strong>Tournament Name</strong></th>
<th><strong>Winner</strong></th>
<th><strong>Runner Up</strong></th>
<th><strong>End Date</strong></th>
</tr>
</thead>
<tbody>
<?php
$count=1;
$sel_query="Select * from tournament_info 
INNER JOIN games ON tournament_info.game = games.id 
INNER JOIN tournaments ON tournament_info.tournament = tournaments.tournament_id
INNER JOIN teams ON tournament_info.winner = teams.team_id

WHERE finish_date <= DATE(NOW());";
$result = mysqli_query($con,$sel_query);

while($row = mysqli_fetch_assoc($result)) { ?>
<td align="center"><?php
    if(!empty($row["game_url"])){ ?>
        <a href="<?= $row["game_url"]; ?>"><?= $row["game_name"]; ?></a>
    <?php }else {
        echo $row["game_name"];
    }
    ?></td>
     <td align="center"><?php
    if(!empty($row["tournament_url"])){ ?>
        <a href="<?= $row["tournament_url"]; ?>"><?= $row["tournament_name"]; ?></a>
     <?php }else {
        echo $row["tournament_name"];
    }
    ?></td>

<td align="center"><?php
    if(!empty($row["team_url"])){ ?>
        <a href="<?= $row["team_url"]; ?>"><?= $row["team_name"]; ?></a>
     <?php }else {
        echo $row["team_name"];
    }
    ?></td>
<td align="center"><?php echo $row["runner_up"]; ?></td>
<td align="center"><?php echo date('F j, Y', strtotime($row["finish_date"])); ?></td>

 </tr>
<?php $count++; } ?>
</tbody>
</table>
</html>

Live Table on Website

mysql table

mysql table

1 Answers

You need to join on teams twice, once for the winner and once for the runner-up:

SELECT     *
FROM       tournament_info 
INNER JOIN games ON tournament_info.game = games.id 
INNER JOIN tournaments ON tournament_info.tournament = tournaments.tournament_id
INNER JOIN teams t_winner ON tournament_info.winner = t_winner.team_id
INNER JOIN teams t_runner ON tournament_info.winner = t_runner.team_id
Related