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.

 

[jimbopalmer]

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 I tried to remain child-like, all I acheived was childish.

 

[woodpd]

Cheers mate,

I never knew you could create aliases for table names (or whatever you call it). I've had that problem before and side stepped the issue with 2 queries (crap I know!!).

Ingenius (or maybe I should just learn SQL properly!)

ChoPa