Topic: 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:

Code: [Select]

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.