CODEWITHSUNDEEP
mysql
user1343099
user1343099

Reputation: 49

Listing highscores for each level using mysql

I have the following 2 database structures:

Levels:

CREATE TABLE IF NOT EXISTS `levels` (
  `lid` int(6) NOT NULL auto_increment,
  `name` varchar(25) NOT NULL,
  `levelcode` mediumtext character set utf8 collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`lid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

and highscores:

CREATE TABLE IF NOT EXISTS `highscores` (
  `lid` int(11) NOT NULL,
  `userid` int(6) NOT NULL,
  `score` int(8) NOT NULL,
  PRIMARY KEY  (`lid`,`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

What I would like to do is get a list of all the levels, as well as if a particular user has a high score, the top score, or no score at all. For this example lets use userid '2'

This is an example table of results I hope to achieve: (sorry dont know how to make a table)

Level ID,  Level Name,        ScoreStatus
1,         firstlevel,        noscore
2,         secondlevelname,   best score
3,         third level name,  has score

and so on..

Is there a way to do this with a single query?

Upvotes: 0

Views: 108

Answers (2)

DRapp
DRapp

Reputation: 48139

You can do by getting both the MAX of ALL, and the MAX of a particular user by just going against the HighScores table... then, join to the user table and qualify what message you want to display via a left-join. So, the inner-most prequery does on a per "LID" the highest score of all users, and another for the actual score for that user. 

select
      L.lid,
      L.Name,
      L.LevelCode,
      if( PreQuery.HighScore is null, 'No Score', 'Score' ) as HasAScore,
      coalesce( PreQuery.HighScore, 0 ) as HighScore,
      if( PreQuery.UserHS > 0, 'User Has Score', 'No User Score' ) as UserHasAScore,
      coalesce( PreQuery.UserHS, 0 ) as UserHighScore,
      if( PreQuery.UserHS > 0 AND PreQuery.UserHS = PreQuery.HighScore, 
             'Use HAS The High Score', 'not the high score' ) as DoesUserHaveHighScore
   from
      Levels L
         LEFT JOIN ( select
                           HS.LID,
                           MAX( HS.Score ) as HighScore,
                           MAX( IF( HS.UserID = UserIDYouAreCheckingFor, HS.Score, 0 )) as UserHS
                        from
                           HighScores HS
                        group by
                           HS.LID ) PreQuery
            ON L.lid = PreQuery.LID

Upvotes: 1

paddy
paddy

Reputation: 63471

You can use GROUP BY and MAX() to find the highest score for each level, then use a LEFT OUTER JOIN to select either the highest score or NULL:

SELECT s.lid, lvl.name, MAX(s.score)
    FROM levels AS lvl
    LEFT OUTER JOIN highscores AS s ON lvl.lid = s.lid
    GROUP BY s.lid;

If you want to restrict this by userid 2 then add a WHERE userid=2 before the GROUP BY.

ok, so MAX( s.score ) gets me the best score for that level, how would I go about getting userids 2 score to compare his score and see if its the best?

Ahh, right... Well, I'm not in front of any MySQL at the moment and can't quite remember the details confidently enough to write it without testing... In the simplest way, you would expect that the score selected from the highscores table when using MAX() will select the whole row, thus userid would be paired with the score and you could just check if it is equal to 2 or not.

But I have a feeling you have to internally sort that table (by score) in reverse so that the GROUP BY clause puts the userid with the highest score in the first row, thus selects that particular user. It's all a bit klunky for me.

So what you can do instead is keep that original query and join the whole thing back to the user table. You then test whether the user's score is equal to the high score. Something like this:

SELECT a.lid, a.name, IF( highscore IS NULL,
                          'no score',
                          IF( highscore = score,
                              'best score',
                              'has score' ) )
  FROM
    (SELECT s.lid, lvl.name, MAX(s.score) AS highscore
        FROM levels AS lvl
        LEFT OUTER JOIN highscores AS s ON lvl.lid = s.lid
        GROUP BY s.lid) AS a
  JOIN
    highscores s ON s.lid = a.lid
  WHERE userid = 2
  ORDER BY lid;

Apologies if this is syntactically incorrect. It ought to be close to what you're after.

Upvotes: 1

Related Questions