Different relationships between 2 tables in one query??

[rablax]

I only recently entered the mySQL/PHP world after discovering that the "powerful" site building tools offered by the host I contracted with were useless, so I'll apologize up front if my problem is too simple or basic for this forum. However, you never know if you don't ask, so...

I'm building a website for a sports team and want to list the game schedule. I have a "game" table with date, time, etc. fields, and a "team" table with team name, school, etc. I set up the games table with a opponent_id field and a host_id field, both of which relate to the team table's team_id field. I want to display a game schedule and list each game's opponent name by using a query "WHERE game's opponent_id = team's team_id". OK, no problem. But, I also want to list the game's host name by adding to the same query "WHERE game's host_id = team's team_id". And, of course, this doesn't work because, I guess, I'm using two different relationships between the two tables in the same query. So a couple of questions:

1. Is there a way to do this, and if yes, how?

2. If there isn't, should I modify the database to split the current team table into a new team table and new location table, and then use a join on 3 tables (game, team, location)? If this is the better solution, I get stuck in the database normalization cunundrum - that is, both team and location tables would have to have a field for school name, which I would think violates redundancy rules.

I'm working with mySQL 5.0.18 and PHP 5.1.2, and any help here would be most sincerely appreciated.

Thanks!

[drifter]

OK this one is not one I am super expert on or anything, but I believe you can do what you need by just joining 2 times..

select * from a AS t1, b AS t2, b AS t3 WHERE t1.id=t2.id AND t1.id=t3.id

not the problem is that t2 and t3 both have a column called say team so when you look at the results for team, what do you get?

instead of *, try defining the fields such as

Select t2.team AS opponent, t3.team AS host, ......

I think this may help

Again not my total area, but maybe a start

[fenway]

t2.*, t3.* work as well, provided you don't have overlapping column names, which you can alias separately anyway -- but yes, joins are the right way to go.