My office is working on a fantasy football database and, unfortunately, I
have been tagged as the DBA. I'm a bit weak on set theory but I'm trying.
Right now I am trying to calculate up game scores into the database rather
than running through code to do that. A baseline of my schema is that:
+ Each team has a schedule number associated with it. The schedule numbers
are linked to the game schedule table.
+ There is a game schedule table based on schedule numbers. This schedule is
fixed and doesn't change season to season. If you want to change who plays
who you change the team schedule numbers.
+ GameScheduleDetails is a view joining the team info with the schedule so I
can easily see who plays who.
+ There is a game roster table that lists which players each team played in
a given week.
+ After running stats through there is a table that lists the points for
each player per week.
+ GamePointDetails is a view joining the game rosters with the
weeklyplayerpoints.
+ The games table is a historical table that shows the scores between two
teams for a given week and season. The SQL to run the calcs is below.
I know that what is happening is that the join between the two teams scores
is generating 64 rows of player points for each team instead of eight (a
game roster consists of eight players). That means that the join that is
occuring is a natural join rather than the desired inner join which leads to
a multiplicative row count of 8 players from team 1 x 8 players from team 2
which is 64 rows per team. Since each player score is being counted 8 times
I am just dividing the sum of each team score by 8 and it is spitting out
the correct numbers. But, it is not working "properly". It is getting the
job done but I want to understand is how to make it work properly where I
don't have to do the divide by 8;
So my question is... can I get rid of the divide by 8 or am I stuck with it?
rjsjr
insert into games
(
season, weeknum,
teamid1, team1pointsscored, team1maxpointsfor,
teamid2, team2pointsscored, team2maxpointsfor
)
select
SEASON_NUM as Season,
WEEK_NUM as WeekNum,
gsd.team1id as Team1ID,
sum(gpd1.points) / 8 as Team1Score,
0 as Team1Max,
gsd.team2id as Team2ID,
sum(gpd2.points) / 8 as Team2Score,
0 as Team2Max
from
gamescheduledetails gsd
inner join gamepointdetails as gpd1 on
gpd1.teamid = gsd.team1id and
gpd1.weeknum = gsd.weeknum
inner join gamepointdetails as gpd2 on
gpd2.teamid = gsd.team2id and
gpd2.weeknum = gsd.weeknum
where
gsd.weeknum = WEEK_NUM and
gpd1.leagueid = gpd2.leagueid and
gpd1.season = gpd2.season and
gpd1.season = SEASON_NUM
group by
gsd.team1id, gsd.team1name, gsd.team2id, gsd.team2name
;
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
