you don't need a played field unless it is possible a game is not played on the date scheduled. Otherwise if your date has passed then the game has been played.

also what is the vs column for? and did you forget to type ascore?

what are the points columns for?

in your additional post you don't say what legsw or legsl stand for either.

here is something you might be able to adapt.

There is a separate team table with a teamid and teamname field and then the main table with

hometeam,

homescore,

awayteam,

awayscore

You end up with a games played, wins, losses, ties, points in the final table.

Code:

SELECT
t.teamname as Tm
, (sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 3 ELSE 0 END) + sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END)) AS P
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) THEN 1 ELSE 0 END) as GP
, sum(CASE WHEN (
g.hometeam = t.teamid AND g.homescore > g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore > g.homescore
) THEN 1 ELSE 0 END) AS W
, sum(CASE WHEN (
g.hometeam = t.teamid OR g.awayteam = t.teamid
) AND g.homescore = g.awayscore THEN 1 ELSE 0 END) AS D
, SUM(CASE WHEN (
g.hometeam = t.teamid AND g.homescore < g.awayscore
) OR (
g.awayteam = t.teamid AND g.awayscore < g.homescore
) THEN 1 ELSE 0 END) AS L
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END) as GF
, SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END) as GA
, (SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.homescore
WHEN (g.awayteam = t.teamid) THEN g.awayscore
END) - SUM(CASE
WHEN (g.hometeam = t.teamid) THEN g.awayscore
WHEN (g.awayteam = t.teamid) THEN g.homescore
END)) as GD
from teams t
left join fixtures g
on t.teamid in (g.hometeam,g.awayteam)
GROUP BY t.teamname
ORDER BY P desc, GP desc