Score aproach in a football league db

[miguelus]

Hello:
I think my table structure is not unusual: TEAM, SEASON, WEEK, GAME

In GAME I have:
game_id
week_id (fkey)
homeTeam_id
homeTeamScore
guestTeam_id
guestTeamScore

I am getting nuts to display stats as simple as a team list with total goals.

Now I am trying to get a SUM(homeTeamScore) and a SUM(guestTeamScore), and it requires a WHERE (homeTeam_id = team_id OR visitorTeam_id = team_id)

But it doesnt work as desired.

Maybe I need a different table aproach?, like a SCORE table, or something like that.

Than you in advance

[guelphdad]

Here is what I use, my table is quite similar to yours. Not sure if you are also tallying for wins, losses and points as well as the goals.


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
inner join fixtures g
on t.teamid in (g.hometeam,g.awayteam)
and league= (select leagueid from leagues where leaguename = $leaguename)

GROUP BY t.teamname
ORDER BY P desc, W desc, GD desc

[miguelus]

Great!!, it fits just right.

Even more, it is great for me to study it, now that I am improving my modest skills with more complex queries.

Thank you!!

[guelphdad]

A couple of things for you.

I forgot this line was in there, you of course only need it if you are running multiple leagues and you want to pass a league value to determine which table you are running:

and league= (select leagueid from leagues where leaguename = $leaguename)

The standard use of the IN clause is used like this:


select footballteam
from yourtable
where teamname IN ('Manchester U', 'Chelsea','Liverpool')


But the IN clause above checks for one value in more than one column

where 'Chelsea' in (hometeam, awayteam)
for example.

Oh, and last, but most important in my opinion. Check the manual for use of the CASE clause, it is very helpful in a number of scenarios. Kind of like the IF clause in programming really.

[miguelus]

I forgot this line was in there, you of course only need it if you are running multiple leagues and you want to pass a league value to determine which


OK, thanks, I have already did it when adapting your query to my system. I have passed parameters with the id of the league and the category (junior, senior, etc), so I have incorporated them to the WHERE clause.


Oh, and last, but most important in my opinion. Check the manual for use of the CASE clause, it is very helpful in a number of scenarios. Kind of like the IF clause in programming really.

Yeah! some kind of a new world for me. :-)

Regards.