PHP and MySQL syntax for join statement

[sladotron]

Hi,

I don't do too many SQL queries, so I am unsure how to get this working in php.

I am trying to join two tables using the join command, but I can't get the syntax correct to get what I want.

 

I have two tables. One table called "teams" has the name of the team (TeamName), their unique ID (TeamID) and where they play at (FacilityName)

-----------------------------------

TeamID | TeamName | FacilityName |

------------------------------------

 

The second table called "meets" has id of the home team, id of the away team, the date when they played and games won for each team.

 

-----------------------------------------------------------------------

HomeTeamID | AwayTeamID | Date | HomeGamesWon | AwayGamesWon |

-----------------------------------------------------------------------

 

I need to lookup the TeamName from the "teams" table that corresponds to the ID stored in the meets table for Home team and for Away team. So trying to join based on TeamID. So I want a table that looks like this

 

---------------------------------------------------------------------------------------

HomeTeam Name | AwayTeam Name | Date | HomeGamesWon | AwayGamesWon |Facility |

---------------------------------------------------------------------------------------

 

I believe I have to use table aliases in order to do a join statement twice. Once for HomeTeam Name and once for AwayTeam Name.

 

Here is the query in php that I wrote that gives me an error saying that the syntax is wrong.

 

$query_Recordset3 = "SELECT meets.Date, temp1.TeamName AS HomeTeam, temp2.TeamName AS AwayTeam, meets.HomeGamesWon, meets.AwayGamesWon, teams.Facility";

$query_Recordset3 .="FROM teams ";

$query_Recordset3 .="INNER JOIN meets AS temp1 ";

$query_Recordset3 .="ON temp1.HomeTeam=teams.TeamID ";

$query_Recordset3 .="INNER JOIN meets AS temp2 ";

$query_Recordset3 .="ON temp2.AwayTeam = teams.TeamID ";

$query_Recordset3 .="ORDER BY `Date`";

$Recordset3 = mysql_query($query_Recordset3, $ttleague) or die(mysql_error());

$row_Recordset3 = mysql_fetch_assoc($Recordset3);

$totalRows_Recordset3 = mysql_num_rows($Recordset3);

 

If you could point me in the right direction, I would really appreciate it.

Thanks

[sladotron]

Solved. I used a workaround and did two queries, one for the home team name and the second for the away team, so I ended up with two recordsets, but its ok for my purposes.

[Barand]

You were almost there.

 

<?php

$query_Recordset3 = "SELECT m.Date, t1.TeamName AS HomeTeam, t2.TeamName AS AwayTeam, 
        m.HomeGamesWon, m.AwayGamesWon, t1.Facility
        FROM meets m
        INNER JOIN teams t1 ON m.HomeTeamId = t1.TeamId
        INNER JOIN teams t2 ON m.AwayTeamId = t2.TeamId 
        ORDER BY m.`Date` ";

?>