Show on list but not counted?!

I’m making a leaderboard list and works just fine, but now its getting tricky… A player is shown on the list as soon a score is submitted.

But what if I want to show all users signed up for the leaderboard even though a score is not yet submitted…

When a user is signing up a record in the leaderboard DB is submitted with “0” as score, matchid as “0” and aproved set as “0”.

When a score should get into the eqation there should be a matchid > 0 and aproved set as “1”… Can this be done…

The problem here is that the leaderboard is based on the avreage score… The “0” from the signup should then not count… A score submitted though could be “0” and thats ok…

Here is what I have gor working so far…

SELECT u.fname
     , u.lname
     , u.country 
     , u.seouname
     , u.new_userid
     , up.points 
     , COUNT(up2.points) + 1 AS rank
  FROM ".$prefix."_users u  
INNER 
  JOIN ( SELECT AVG(point) points
              , userid
           FROM ".$prefix."_LC_leaderboard WHERE lcSeason = '$currSeason' AND aproved=1 AND matchid > 0           
         GROUP 
             BY userid HAVING COUNT(userid) >= 1) up 
    ON u.new_userid = up.userid         
LEFT OUTER 
  JOIN ( SELECT AVG(point) points
              , userid 
           FROM ".$prefix."_LC_leaderboard WHERE lcSeason = '$currSeason' AND aproved=1 AND matchid > 0         
         GROUP 
             BY userid HAVING COUNT(userid) >= 1) up2 
    ON up2.points > up.points
    WHERE u.new_userid = $userid
GROUP
    BY u.new_userid

Hoping for help and thanks in advance…

Turn your INNER JOIN into a LEFT JOIN.

Not quite… I only want the users who has signed up in the LC_leaderboard… Your solution gets all the useres from users table…

Oh okay so not all users are on the leaderboard. Gotcha.

So… (let me make sure i’m getting this right)
Users holds user data. Relevant key field being userid.
LC_Leaderboard holds… multiple records from an individual user containing points, a season, a tinyint approved flag, and a matchid (which presumably is a FK that doesnt really matter to this function other than to say it’s not 0.)

You want to retrieve all users, that have some entries in the leaderboards, but you dont want to count the 0 entry. And then you want to retrieve… something… else? I dont get the whole up2 table bit.

SELECT <userfields>, AVG(points)
FROM users
LEFT JOIN leaderboard ON leaderboard.userid = user.userid
GROUP BY leaderboard.userid
WHERE leaderbaord.seasonid = $myseasonid AND leaderboard.approved = 1 AND leaderboard.matchid != 0;

Is where I’m at atm.

starlion, you can’t put WHERE after GROUP BY

and if you have WHERE conditions on the right table, then the LEFT OUTER JOIN is crippled and becomes in effect an inner join

Tryid it like this:

SELECT u.fname
     , u.lname
     , u.country 
     , u.seouname
     , u.h_index
     , u.new_userid
     , AVG(point)
FROM ".$prefix."_users u 
LEFT JOIN ".$prefix."_LC_leaderboard lc ON lc.userid = u.userid
WHERE lc.lcSeason = $currSeason AND lc.aproved = 1 AND lc.matchid != 0  
GROUP BY lc.userid

But with no luck… Unknown column ‘u.userid’ in ‘on clause’?

Any ideas?

because… you dont have a userid field in your user name, you have new_userid ? (which…makes it all the more confusing?)

i wouldn’t’ve done this, but since you did, you should be using an INNER JOIN

My bad… But after change it still only takes the records with no matchid…

It should take records with matchid=0 and aproved=1 but not count them in the AVG(point)?

Hope this makes sense?

Yeah i’m not sure why you did either. Personally I’d just do a flag on the user table… but anyway!

Now I’m at…
SELECT <userfields>, (SUM(points) / MAX(COUNT(points)-1,1) AS avg
FROM users
INNER JOIN leaderboard ON leaderboard.userid = users.new_userid
WHERE leaderbaord.seasonid = $myseasonid AND leaderboard.approved = 1
GROUP BY leaderboard.userid;

still not sure what the ‘rank’ was supposed to be in your original query though.

Can see the error from the trees…

SELECT u.fname
     , u.lname
     , u.country 
     , u.seouname
     , u.h_index
     , u.new_userid
     , SUM(point) / MAX(COUNT(point)-1,1) AS avg
FROM ".$prefix."_users u 
LEFT JOIN ".$prefix."_LC_leaderboard lc ON lc.userid = u.new_userid
WHERE lc.lcSeason = $currSeason AND lc.aproved = 1  
GROUP BY lc.userid

Gives me this error:
Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘1) AS avg FROM fb_users u LEFT JOIN fb_LC_leaderboard lc ON lc.us’ at line 7

Any ideas?

er because i’m not coding in PHP i’m coding in mySQL. Silly me. Mind isnt in it today it seems lol.

Strip the max out and just do COUNT(point) - 1… if my mind hasnt COMPLETELY left me, MySQL returns a NULL on division by 0… which would give you the difference between someone who’s scored 0 in all their matches, and someone who’s never had a match.