CODEWITHSUNDEEP
foreign-keys
user1228078
user1228078

Reputation: 3

one table with 2 fields referencing to the same table

I have a table (GAMES) containing 11 fields total (including the ID, primary key). 2 fields of this table reference (via foreign keys) to the same external table (TEAMS table), these are the HOME_TEAM and AWAY_TEAM fields

The table TEAMS contains all the teams, including their name, address, logo, etc.

My problem is as follows:

As i reference to the same table for 2 fields in the GAMES table how can i display these correctly. When I mean display i mean that i want to display the name of the team (stowed in the table TEAMS), and not the foreign key which is stowed in the table GAMES. I get to display the foerign keys correctly, but how can i display the name of the team instead?

Any help much appreciated, thanks.

This is what i have now:

mysql_select_db($database_check_mag, $check_mag);
$query_getPosts = "SELECT games.id_game, games.seizoen, games.date, games.type, games.hometeam, games.awayteam, teams.id_team, teams.naam_team, teams.logo, teams.adres FROM games INNER JOIN teams ORDER BY games.date DESC";
$query_limit_getPosts = sprintf("%s LIMIT %d, %d", $query_getPosts, $startRow_getPosts, $maxRows_getPosts);
$getPosts = mysql_query($query_limit_getPosts, $check_mag) or die(mysql_error());
$row_getPosts = mysql_fetch_assoc($getPosts);

And this is how i display it:

<table width="100%">
<tr>
<th scope="col" align="left">Date</th>
<th scope="col" align="left">Type</th>
<th scope="col" align="left">Game</th>
<th scope="col">&nbsp;</th>
<th scope="col">&nbsp;</th>
</tr>
<?php do { ?>
<tr>
<td align="left"><?php echo $row_getPosts['date']; ?></td>
<td align="left"><?php echo $row_getPosts['type']; ?></td>
<td align="left"><?php echo $row_getPosts['hometeam']; ?> - <?php echo $row_getPosts['awayteam']; ?></td>
<td><a href="games_edit.php?id_game=<?php echo $row_getPosts['id_game']; ?>">EDIT</a></td>
<td><a href="games_delete.php?id_game=<?php echo $row_getPosts['id_game']; ?>">DELETE</a></td>
</tr>
<?php } while ($row_getPosts = mysql_fetch_assoc($getPosts)); ?>
</table>

So now the echo $row_getPosts['hometeam'] and echo $row_getPosts['awayteam'] are displaying the id_team fields correctly but i want them do display the naam_team fields, which are different of course...

Upvotes: 0

Views: 138

Answers (1)

yael alfasi
yael alfasi

Reputation: 682

you need to join the teams table twice
once for each team
like this:

select g.id_game, homet.naam_team, awayt.naam_team from games g, teams homet, teams awayt where g.hometeam = homet.id_team and g.awayteam = awayt.id_team;

Upvotes: 1

Related Questions