I'm trying to figure this out. I want to list a leaderboard with all players listed in my table. The leaderboard is created by adding the points the player has earned and that works fine, but... I only wants to add points if a certain criteria is fullfilled. The record should have and matchid higher than(>) zero.
Here is my script:
$sql = "SELECT u.fname, u.lname, u.country, user_points.points FROM ".$prefix."_club_users cu
INNER JOIN ".$prefix."_users u ON u.new_userid = cu.new_userid
INNER JOIN ( SELECT AVG(point) points, userid FROM ".$prefix."_club_leaderboard
WHERE seasonid = $seasonid
GROUP BY userid ) user_points ON cu.new_userid = user_points.userid
WHERE cu.clubid = '$clubid' ORDER BY user_points.points DESC";
The easy way would be to put an "AND matchid > 0" into the:
INNER JOIN ( SELECT AVG(point) points, userid FROM ".$prefix."_club_leaderboard
WHERE seasonid = $seasonid
GROUP BY userid ) user_points
If I do this it only shows the members which have played atleast one match, but I want to show all the players regardless if a player has played or not. The reason a player has a record with no matchid is so they are listed on the leaderboard.
If I don't insert a "AND matchid > 0" the 0 points would be taking into the avg eqation and this is no good due to the only reason this record exists is to be seen in the leaderboard.
Hope this makes some sense and somebody is able to help in this matter!
