calculate rank in highscore from 2 tables

Question

i have a trivia game and i want to reward users for 2 events:
1) answering correctly
2) sending a question to the questions pool

i want to query for score and rank of a specific player and i use this query:

SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank 
FROM   (  trivia_players 
          JOIN (  SELECT COUNT(*) AS sent, senderid 
                  FROM trivia_questions 
                  WHERE senderid='$userid'
               ) a 
          ON trivia_players.userid=a.senderid
       )  
       ORDER BY score DESC

and it works if the player is in both tables i.e answered correctly AND sent a question. but it doesn't work if a player hasn't sent a question

any idea how to fix this query? ($userid is the given parameter)

thanks!

Thanks Tom! only problem is the ranks are not correct:
userid score rank
58217 380 1
12354 80 3
32324 0 2

Answer 1

Have you tried using a RIGHT JOIN or LEFT JOIN? Just off the top of my head!

Answer 2

I would probably do it like this:

SELECT
    user_id,
    score,
    rank
FROM
(
    SELECT
        TP.user_id,
        (TP.correct * 10) + (COUNT(TQ.sender_id) * 30) AS score,
        @rank:=@rank + 1 AS rank
    FROM
        Trivia_Players TP
    LEFT OUTER JOIN Trivia_Questions TQ ON
        TQ.sender_id = TP.user_id
    GROUP BY
        TP.user_id,
        TP.correct
    ORDER BY
        score DESC
) AS SQ
WHERE
    SQ.user_id = $user_id

I don't use MySQL much, so the syntax may not be perfect. I think that you can use a subquery like this in MySQL. Assuming that MySQL handles COUNT() by only counting rows with a non-null value for , this should work.

The keys are that you do a COUNT over a non-null column from Trivia Questions so that it counts them up by the user and you need to use a subquery so that you can get ranks for everyone BEFORE constraining to a particular user id.