SQL Statement: Having difficulty combining into one statement

UrbanCondor

Hi guys,

I am using PHP version 5.2.6 with a MySQL database.

I don't have an error at all with anything, I am just struggling to figure out how to write a MySQL query that returns all the variables that I wish it to return and I was hoping there might be some SQL gurus out there that could bestow upon me their wisdom.

So, here it goes...

I am trying to create a sports site where I can enter details of sports fixtures (games) and their results, then view the results in a ladder table (see the sports_ladder image below).

The problem I am having is that I don't know how to include all of this in one SQL query. The only column I am struggling to return is the 'Against' column, which returns the total amount of points scored against the particular team listed.

The reason I want this all in one query is so that I can order the column by the 'Total Points' column, then by the '+/-' (which is calculated as 'For' minus 'Against').

Now, as far as i know the 'Against' column will need to be in the same query as all the other data in order to get an 'ORDER BY' to work. Please correct me if I am wrong. I know how to do it using 2 seperate queries, but 2 queries is no good to me.

In my frustration I have created a simplified version of my project (in the hope that it would grant me clarity - it didn't). What I am hoping is that someone wouldn't mind looking at the simplified version of my project to see what I have done and what I am displaying. It basically consists of 4 small PHP files and I have tried my best to add as many comments to the code as possible so it is easy to follow.

My simplified project is attached to this post (web.zip).

Just incase you can't acces it, please feel free to PM me and I will provide you with a link or e-mail it to you if you like.

Many, many thanks in advance.

UrbanCondor

laserlight

It would be a good idea to tell us what are the tables involved, their relevant columns, how they are related, and what exactly are you trying to select (although that is already provided by the HTML table you have shown).

UrbanCondor

laserlight;10913955 wrote:
It would be a good idea to tell us what are the tables involved, their relevant columns, how they are related, and what exactly are you trying to select (although that is already provided by the HTML table you have shown).

Thanks for the reply. All this information is in the zip file I provided, however at the bottom of this post is a picture of the database structure. Please let me know if you require any more info.