[SOLVED] having troubl with query joins

[chiprivers]

I have a table (teams) that contains a list of football teams:

 

teamID      int

team        varchar

 

and a further table (fixtures) that contains a list of fixtures:

 

fixtureID    int

date        date

home        int (refers to teamID in teams table)

away        int (refers to teamID in teams table)

 

I would like to query the fixtures table to get fixtures of a specific date (easy enough) but would also like to return the team name of both the home and away teams.

 

At the moment I have:

 

SELECT home, away FROM fixtures WHERE date = $date

 

this will obviously only return the teamID values, how do I add a join to get the team names from the teams table at the same time instead of getting the ID's?

[fenway]

You'll need to JOIN in the teams table twice:

 

SELECT f.*, h.team AS homeTeam, a.team AS awayTeam 
FROM fixtures AS f 
LEFT JOIN teams AS h ON ( h.teamID = f.home ) 
LEFT JOIN teams AS a ON ( a.teamID = f.away ) 
WHERE f.date = '$date'

 

Hope that helps.

[chiprivers]

Thanks for your help Fenway, got it working perfect however I did have to tweak it cos you got the initials mixed up in your query, you put h.team and then joined as t!

[fenway]

Sorry about that... fixed now.