League Database and Query

[Mr Rich UK]

Hi there,

 

I run a site where people play sports games against each other.

 

Now I've been looking to create a system which updates standings automatically and I think I am getting close, but I just needed a little help to get over a few problems.

 

I'm guessing it's sensible to separate the data into two tables, first one probably containing the following, let's call it 'madden_teams'

 

team_id, team_name

 

then have a table for the results, which we'll call 'madden_results'

 

result_id, away_team, away_score, home_team, home_score, week

 

I know this has something to do with joins, and I have looked into it, but I am confused by how you can use the recognition over how one result is larger than the other to get the standings.

 

So onto the query I need to do.

 

Obviously I'm going to have to do something like:

 

SELECT team_name, Sum(W), Sum(L), Sum (D)

 

To get my wins and losses, but how would I go about trying to build the rest of the query to show wins and losses correctly for each team?

 

I feel like I'm stepping up a level here and I don't really understand how you can do this.

 

[Mr Rich UK]

Sorry, just realised I've posted this in the wrong section.