[SOLVED] Sub query select?

[ninedoors]

I'm not sure if I can do this in one query or not without a sub query.

 

I have a schedule table that holds all the information about a hockey league schedule such as game_id(PRIMKEY), datetime, location, hometeam(INT), awayteam(INT), etc.

 

Then I have a another table that holds the teams names that play in the league.  It has a team_id column and then a name column.

 

How can get out the game information with the team names in one query.  Everything I try I can only get one of the names.  Here is the latest query I tried:

 

'SELECT sch.game_id, sch.game_date, t.alt_name FROM {db_prefix}osm_schedule as sch
                    LEFT JOIN {db_prefix}osm_teams as t ON(sch.hometeam = t.team_id)
                    LEFT JOIN {db_prefix}osm_teams as t ON(sch.awayteam = t.team_id)
                    WHERE sch.game_date >= NOW()  
                    ORDER BY sch.game_date
                    LIMIT 0, 1', 
                    array(
                        'team' => $context['user']['teams'][0],
                        ));

 

I am using the SMF structure for my site.  Thanks for the help.

 

Nick

[fenway]

That's because you're using the same alias twice.