Compare 2 values at the same row

[aliento]

Hi i have a site (for personal use) which i see the soccer games stats.

I have done a lot comparιson between the goals scores a team and the goals accepts at the last 6 games.

I compare those goals and i see the best attacks and the best defences.

Now i stuck as i want to see the games with best attacks and the worst defence.

I have all the games haven't started at the table 'games', within i have :

 

table : games

--------------------------------------------------------------------------------------------------

| id | game_id | home_attack | home_defence | away_attack | away_defence |

--------------------------------------------------------------------------------------------------

home_attack : goals achieved the home team in last 6 games

home_defence : goals accepted the home team in last 6 games.

The same for the away team.

 

I want to find out from the db the best 30 matches with the best home_attack and the worst away_defence .

 

The mysql can do this action ?

 

I tried to add another field with a calculation which was : home_attack + away_defence but it didn't work.

 

The site is in Greek is here balllab.net

[Psycho]

Well, to start, the database is not set up correctly and you are making it harder than it needs to be.

 

 

 

home_attack : goals achieved the home team in last 6 games

home_defence : goals accepted the home team in last 6 games.

 

You should not store 'calculated' data such as this. Instead, you create your queries to calculate the data dynamically. In fact, your explanation doesn't make sense to me. If the table is 'games' wouldn't you store the data for each individual game in that table?

[aliento]

Hi Psycho 

I have the game id and there are the game info i also have and the teams table with stats for the teams. But i want only this,

 

 

sorry for the double post i cant delete it.