Matching Teams to ID's

NZ_Kiwis

I have two tables, table 1 is games, which has all the info on a game, team1ID and team2ID. I want to link to my teams table in one SQL search so I can team1ID's name and team2ID's name.

I need to join but how can I do that?

NZ_Kiwis

This is how my tables are set up

Table1: Games

ID
Team1ID
Team2ID
Result
GameTime

Table2: Teams
ID
TeamName

bradgrafelman

Does the order of the teams matter in the games table? In other words, could you not interchange Team1ID and Team2ID? If it doesn't, then I'd say your schema isn't normalized and you should in fact have three tables; one for teams, one for games, and one that relates a team and a game.

NZ_Kiwis

Team1 is the home team and Team2 is the away team so it does matter

bradgrafelman

Ah, so it's not a case of a lack of normalization - just a lack of descriptive column names. Gotcha. 😉

In that case, what have you tried? Seems like what you'd want to do is join (LEFT or INNER) Table1 to Table2 twice; once with a join condition to match Table1.Team1ID with Table2.ID, and a second to match Table1.Team2ID with Table2.ID. (You'd get Team1's name from the first join and Team2's name from the second join.)

NZ_Kiwis

I have this and it works in phpMyAdmin.

I now need to join results_games.AwayTeam to a results_team.ID.

How can I do that?

SELECT * FROM results_games

JOIN results_teams ON results_games.HomeTeam = results_teams.ID 

WHERE results_games.ID = 1 AND results_games.GroupID =1

bradgrafelman

Give the first JOIN a name (e.g. "JOIN results_teams t1"), then repeat the JOIN (with a new name, of course) for the other team. You'd then use t1.TeamName and t2.TeamName, respectively, for the home and away team names.

NZ_Kiwis

Great that works in phpMyAdmin

As both team names are coming from the same table how do i echo our the different names??

echo $row['TeamNameShort']

prints the first team name only

bradgrafelman

First, get rid of the "SELECT *" (and never use it again 🙂). Next, explicitly SELECT the two items you desire (in addition to the other column(s) of interest - omitted here for brevity):

bradgrafelman;11033149 wrote:
You'd then use t1.TeamName and t2.TeamName, respectively, for the home and away team names.

You can of course make it cleaner in the code if you'd like by using aliases... e.g. "SELECT t1.teamName homeTeamName, t2.teamName awayTemaName, ...".

NZ_Kiwis

THanks, but how can I then echo t1.teamName and t2.teamName as the columns from teams is just called TeamName and when i echo that i get Team1 only being the first column

bradgrafelman

You'd either echo $row['t1.teamName'] or you'd echo $row['homeTeamName'] if you used my alias example above.