Posted by aaron.reese on 10/17/06 11:24

Hi guys, I'm having some trouble getting a query to return the
information I require.
Tables:
EventMaster holds the venue details
Event holds the date of the event and some other irrelevant
information, linked to EventMaster by EventMasterID
Results holds the finishing positions for the event plus indicators for
bonus points, linked to Event by EventID
Users holds the details of the players, linked to Results by
MemberID/MembershipNo (I know.. I know!)
Scoring holds the points available for each position (only top 8 score
points).

I am producing a results set that shows the total points for a season
for each player, sorted by total points.

The following query works...


function fn_DoSeasonResultsSQL($EventMasterID,$SeasonID)
{
$SQLQUERY = "SELECT
EM.EventMasterID,
R.MemberID,
U.Fname,
U.Nname,
U.Sname,
sum((10 + (if(S.Points is NULL,0,S.Points)) + (R.Card * 10) +
(R.Sunglasses * 10) + (R.Assist * 10) + (R.Hat * 10) + (R.Cigar * 10)
)) as GameTot
FROM
EventMaster as EM left join
Event as E on EM.EventMasterID = E.EventMasterID join
Results as R on R.EventID = E.EventID join
User as U on R.MemberID = U.MembershipNo left join
Scoring as S on R.Position = S.Position and S.SeasonID = $SeasonID

WHERE
EM.EventMasterID = $EventMasterID and
E.SeasonID = $SeasonID

GROUP BY
R.MemberID,
EM.EventMasterID
ORDER BY
EM.EventMasterID,
GameTot DESC";

return $SQLQUERY;
}

What I now want to do is write a new query that calculates points
differently becasue I want to see what effect a different points system
would have on the league table.

The points scored will be a function of your finishing position and the
number of players in that event so the more players, the more points
you score. The formula is simple enough in that points = (#players in
event - finishing position ) squared.

I need to get the number of players for each event
[count(Results.EventID) where Results.EventID = Event.EventID] and do
the necessary calculation.

The following query gives me the points total for each player for each
event.

SELECT
EM.EventMasterID,
R.MemberID,
U.Fname,
U.Nname,
R.Position,
U.Sname,
(( 10 + ( IF(S.Points IS NULL , 0, S.Points) ) + ( R.Card *10 ) + (
R.Sunglasses *10 ) + ( R.Assist *10 ) + ( R.Hat *10 ) + ( R.Cigar *10 )
)
) AS GameTot,
(count( Z.EventID ) ) AS NumbPlayers,
(count( Z.EventID ) - R.Position) AS Beat,
((count( Z.EventID ) - R.Position + 1) * ( count( Z.EventID ) -
R.Position +1 ) ) AS NewGamePts,
((count( Z.EventID ) - R.Position + 1) * ( count( Z.EventID ) -
R.Position +1 )) +10 + ( R.Card *10 ) + ( R.Sunglasses *10 ) + (
R.Assist *10 ) + ( R.Hat *10 ) + ( R.Cigar *10 ) AS NewNewTot
FROM
EventMaster AS EM
LEFT JOIN Event AS E ON EM.EventMasterID = E.EventMasterID
JOIN Results AS R ON R.EventID = E.EventID
JOIN User AS U ON R.MemberID = U.MembershipNo
LEFT JOIN Scoring AS S ON R.Position = S.Position AND S.SeasonID
=1
JOIN Results AS Z ON Z.EventID = E.EventID
WHERE
EM.EventMasterID =2 AND E.SeasonID =1
GROUP BY
R.EventID,
R.MemberID
ORDER BY
R.EventID,
R.Position


but I cant get the syntax right to roll up all of the results and give
me a seasons total for each player

[Back to original message]