SQL query

Anon

I have two tables in a mysql database which are for a soccer related program. The matches table has a home team ID (int) and away team ID (int) a home score (int) and an away score (int). The second table which is for the team info has an id field (int) and a name field (varchar). I want to be able to produce a query which will give me something similar to below

T1 T2 Score1 Score2

Arsenal Aston Villa 1 0
Man Utd Blackburn 3 1
Chelsea West Ham 0 3

The problem seems to be that i am referencing the team_info.name field twice, does anyone know of a way of producing the above table in one select statement. Thanks

Anon

Use an alias for multiple instances of same table.

SELECT T1.name, T2.name, m.Score1,
m.Score2
/ first instance of team_info table /
FROM team_info T1
INNER JOIN match M ON (T1.id = m.T1)
/ second instance of team_info table /
INNER JOIN team_info T2 ON (m.T2 = T2.id);

bastien

table name = goal

Column Type Size PrimaryKey
ID number 10 Y
HomeTeam varchar 30

AwayTeam varchar 30

HomeScore number 2
AwayScore number 2
GameDate date 10

select * from goal order by GameDate

This assumes that the data is filled in from two drop boxes for the team names ( don't forget to code to ensure that the team doesn't play itself) and two text boxes for the score....

You will need to figure what criteria is needed to get the results to the user,(ie drop box on gameDate?)

hth

Bastien