Help with a Query, please

Databases
1

I have a table named: games
with one record
Fields: id, h_team, g_team
Values: 1, 1, 2

My second table is named: teams
with two records
Fields: id, team
Values: 1, Home
Values: 2, Guest

I am trying to INNER JOIN games.h_team on teams.id AND games.g_team on teams.id
so I can view it in such a manner:

1 Guest at Home

I have tried this:
$query = "SELECT " .
"games.h_team, games.g_team, teams.team " .
"FROM games " .
"INNER JOIN teams on games.h_team = teams.id " .
“INNER JOIN teams on games.g_team = teams.id”;

I just don’t understand JOINS though I am trying to learn. Any help would be appreciated.

2

all you need to do is use table aliases to distinguish the different rows from the teams table that match the home and guest ids


SELECT games.h_team
     , [COLOR="#0000FF"]hteam.team AS home_team[/COLOR]
     , games.g_team
     , [COLOR="#FF0000"]gteam.team AS guest_team[/COLOR]
  FROM games
INNER 
  JOIN teams [COLOR="#0000FF"][B]AS hteam[/B][/COLOR]
    on [COLOR="#0000FF"]hteam[/COLOR].id = games.[COLOR="#0000FF"]h_team[/COLOR]
INNER 
  JOIN teams [COLOR="#FF0000"][B]AS gteam[/B][/COLOR]
    on [COLOR="#FF0000"]gteam[/COLOR].id = games.[COLOR="#FF0000"]g_team[/COLOR]
3

That worked great. Thank you.
I have tried building on it and this works. It only has a problem when ( for example: sp_games.h_mascot equals any ID that isn’t in my sp_mascots table, I get No games found in the code below. )


  $query = "SELECT " .
           "sp_games.h_team, hschool.school AS home_team, " .
           "sp_games.h_mascot, hmascot.mascot AS home_mascot, " .
           "sp_games.g_team, gschool.school AS guest_team, " .
           "sp_games.g_mascot, gmascot.mascot AS guest_mascot, " .
           "sp_games.h_state, hstate.abbr AS home_state, " .
           "sp_games.g_state, gstate.abbr AS guest_state, " .
           "sp_games.week, " .
           "sp_games.year " .
           "FROM sp_games " .
           "INNER " .
           "  JOIN sp_schools AS hschool " .
           "    on hschool.id = sp_games.h_team " .
           "INNER " .
           "  JOIN sp_mascots AS hmascot " .
           "    on hmascot.id = sp_games.h_mascot " .
           "INNER " .
           "  JOIN sp_schools AS gschool " .
           "    on gschool.id = sp_games.g_team " .
           "INNER " .
           "  JOIN sp_mascots AS gmascot " .
           "    on gmascot.id = sp_games.g_mascot " .
           "INNER " .
           "  JOIN sp_states AS hstate " .
           "    on hstate.id = sp_games.h_state " .
           "INNER " .
           "  JOIN sp_states AS gstate " .
           "    on gstate.id = sp_games.g_state " .
           "WHERE sp_games.week = ".$gameweek." " .
           "AND sp_games.year = ".$gameyear." " .
           "AND ( sp_games.h_state = ".$gamestate." || sp_games.g_state = ".$gamestate." )";

  $result = doquery($query, "games");

  if (mysql_num_rows($result) == 0) {
      $page .= "<tr><td>No games found.</td></tr>\
";
4

use a LEFT OUTER JOIN instead of INNER JOIN for any join where you want the row from the left table to be returned even when there is no matching row from the right table