is it possible to have a colum that calculates something?

what i'm wanting to do is calculate if the team has won drawn or lost. I know how do do this in excel,

if team A is at home then
{If homescore > awayscor = W
if awayscore < homescore = L
else D
}
if team A is away then....

Home Team Score Away Team Score Outcome
Team 1 2 Team 12 1 ?
Team 3 1 Team 5 1 ?

etc...

hope anyone can help

jamie

You need a CASE statement.
http://www.mysql.com/doc/en/CASE_Statement.html

[code]
select home_team, home_score, away_team, away_score,
case
when home_score>away_score then 'W'
when home_score<away_score then 'L'
else 'D'
end as outcome
from ......
[\code]

where does this get added?

the case statment goes into the Select statement you're using to retrieve your records

Wouldn't you want to have a W/L/D listed for each team. The win/loss/draw isn't assigned to the game, it's assigned to the teams who played.

Team 1: (W) 13 - Team 23: (L) 8

something like that huh?

Good point. Tracking W/L/D at a team level would certainly make calculating team standings easier. Maybe three tables then:

Team
-team_id (primary)
-team_name

Game
-game_id (primary)
-home_team
-away_team

Score
-team_id (foreign)
-game_id (foreign)
-score
-outcome

i dont need the table calculating as i've already done that.. and calculated the home and away tables..

What i'm wanting is the form for the last 6 games a team has played.

I have selected the games, limited to the last six and now I want to know if the team has w, d or l a game.

It will eventually look like;

Form (Last 6) WWWDDL.

hope this makes sense...

its alright, fixed it!