Hello Site-reference,

I'm trying to build a table of sport competition results. Matches can be stored in a table like:

res
tour | team_home | team_guest | score

i also want to keep team names separately in a different table:

team
team_id | team

The idea is to use integer ids for team_home and team_guest in a table res instead of full names because it's going to be a huge table. Trying to query both tables:

SELECT r.tur, r.score, t.team FROM res r, team t WHERE r.team_home=t.team_id or r.team_guest=t.team_id

But this way or another i can only select "team" once per line. While for the proper table of results (in output) I need to display #tour | team_home | team_guest | score. Is there a way to select info from the same column twice in one query?

Just to make it clearer - team_home and team_guest - can be numbers and i want to replace them with actual team names from the table "team".
What would you suggest?

Thank you

I found this page really helpful when i first started playing with mysql joins

http://en.wikipedia.org/wiki/Join_(SQL)

The exact solution for my question:
http://www.transio.com/articles--tutori … onship.php
http://www.webdesignforums.net/showthread.php?p=210571

Thank you Northie