Impossible SQL query?

[woodpd]

Dear people,

I am tearing my hair out trying to write an SQL statement to do the following:

In Table "Team" amongst other things I have a "TeamID" and a "TeamName". In Table "Match" amongst other things I have a "HomeTeamID" and an "AwayTeamID" that correspond to those in Table "Team".

In my query, I am trying to extract various fields from "Match" along with the related "TeamName" for "HomeTeamID" AND "AwayTeamID". I can retrieve either one of the team names easily using a join. However to collect both seems impossible without getting repeating records.

Any ideas? I would be most grateful.

 

[woodpd]

I have been given a solution already:- Alias the table name. Thanks guys

FYI

Select m.Match_date, t1.Teamname, t2.Teamname from team t1, team t2, match m where m.HomeTeamID = t1.TeamID and m.AwayTeamID = t2.teamID