[SOLVED] Pulling my hair out over query select

[cutter]

I'm building a site to show results of a sports league. Each team has its own page. Each game is represented by a page that contains a link to the two teams and their scores.

 

Because two teams are referenced from the same list of teams, I'm having trouble figuring out how to write the MySQL query.

I can get a table that delivers the right data, but unfortunately with duplicates each row.

 

Here's the code (somewhat simplified for clarity):

 

  SELECT DISTINCT g.date_value AS date, t.name AS hteam, g.homescore AS hscore, t.name AS vteam, g.visitscore AS vscore
            FROM game g, team t
            WHERE t.tid = g.hometeam_tid OR t.tid = g.visitorteam_tid
            ORDER BY g.date_value ASC

 

That delivers a table like this:

Date

Home

H Score

Visitor

V Score

Aug 1, 2007Roadrunners13 Roadrunners14

Aug 1, 2007Bulldogs 13Bulldogs14

Aug 1, 2007Jaguars 32 aguars19

 

I'm guessing I need to merge the data some kind of JOIN, so that each game is listed once, not twice, and show the correct team in the Home and Visitor columns. In my above code, however, any attempts to change to a JOIN fail.

 

All I know is that I've been all over the Internet trying to find a clear way of doing this without any luck.

[cutter]

Solved by doing two left joins.