Soccer League table

Hi, Im trying to put together a database that will keep track of soccer results and display league tables after matches are played.
The problem i have run into is how to display the updated league tables properly.
The results from played matches comes from a table called Match.

MATCHID, LEAGUEID ,ROUNDNO ,MDATE, HOMETEAM ,AWAYTEAM ,
REFEREE , SPECTACTORS ,HOMEGLS_H , AWAYGLS_H ,
HOMEGLS_F ,AWAYGLS_F ,RESULT char (H,D or A)

My league table have to contain the following data :
Teamid,
Played(number of matches each team has played)
Homewins
Homedraws
Homelost
homegoals scored
homegoals conceded
awaywins
awaydraws
awaylost
awaygoals scored
awaygoals coceded
Points

So my question is, is it possible to make a view to get all the information I want from the match table, or do I have to make a separate physical table for the league_table...
I just think I would benefit from having it as a view because i wouldnt have to update the league_table table for every match played, and it would also dramatically reduce data redundancy.

I have managed to put together a view that displays each teams home stats or away stats, but not the complete table. These views are defined as :

CREATE VIEW Hometable as
SELECT teamid, sum(homegls_f) as HF, sum(awaygls_f) as HA, ( sum(homegls_f) - sum(awaygls_f) ) as HDiff
FROM team, match
WHERE hometeam in
(SELECT team.teamid
FROM team, league_teams
WHERE team.teamid = league_teams.teamid AND league_teams.leagueid = 2)
AND team.teamid = match.hometeam
GROUP BY teamid,name
ORDER BY HDiff DESC,HF DESC;

and

CREATE VIEW Awaytable as
SELECT teamid, sum(awaygls_f) as AF, sum(homegls_f) as AA, ( sum(awaygls_f) - sum(homegls_f) ) as ADiff,
FROM team, match
WHERE awayteam in
(SELECT team.teamid
FROM team, league_teams
WHERE team.teamid = league_teams.teamid AND league_teams.leagueid = 2)
AND team.teamid = match.awayteam
GROUP BY teamid
ORDER BY ADiff DESC,AF DESC;

Is there any way to combine these two views to get a complete table, or do I have to make a new view | make a separate league_table table, |or change the structure of my match table ??

Can anyone give me some pointers here ??


Thanks in advance

Arve

i can do this for you, but you will have to define and explain the relationships and differences between _H, _F, H, D, A, scored, and conceded

your Match table is fine

the key trick here is to use a UNION ALL to create two rows for every row in the Match table --

from (
select matchid
, leagueid
, roundno
, mdate
, awayteam as teamid
, hometeam as oppid
, awaygls_h as gls_h
, awaygls_f as gls_f
, homegls_h as oppgls_h
, homegls_f as oppgls_f
from match
union all
select matchid
, leagueid
, roundno
, mdate
, hometeam
, awayteam
, homegls_h
, homegls_f
, awaygls_h
, awaygls_f
from match
) as teammatches

see this thread --
http://dbforums.com/showthread.php?threadid=863102

rudy
http://r937.com/

Thanks for your reply Rudy,

Now for explaining the thinks you wanted :
HOMEGLS_H (Goals the hometeam has scored by Half Time)
AWAYGLS_H (Goals the awayteam has scored by Half Time)
HOMEGLS_F ,(Goals the hometeam has scored by Full Time)
AWAYGLS_F (Goals the awayteam has scored by Full Time)

Scored and conceded would be like Homegls_F ( Homegoals For) and Homegls_A ( Homecoals Against) sorry about all the confusion

The H,D and A is for Home, Draw and Away. (Homewin,Draw, Awaywin)
I just thought I should put something in so I could calculate the points after a match.
I also thought of putting a Points field instead of the RESULT and putting 3,1 or 0 points after each match, but then I would have to have 2 columns, one for HomeTeamPoints and one for AwayteamPoints....I dont know if that is the best sollution though, I just want to make it as easy to query as possible

Another thing I was wondering is when I display a leaguetable ordered by Points, Is there any possible way to actually calculate which posistion each team has in the table 2nd, 3rd etc ?

Thanks again Rudy

Regards

Arve

you have leagueid in the Match table, that's redundant, it's in the Teams table

rudy