#1
  1. No Profile Picture
    PHP-addict
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2001
    Location
    Netherlands
    Posts
    54
    Rep Power
    14

    Complex SQL for leaderboard


    Hi,
    I am having serious issues trying to retrieve some info from a database.
    I have a scores table, consisting of:
    id: int (autoincrement)
    t_id: int (tournament id, points to tournaments table)
    uid : int (user id, points to user table)
    score: string (score, recorded for that tournament)
    date: datetime (date score was entered)

    Every entry holds a score for a specific user in a specific tournament.
    I would like to retrieve an array that holds the uid as the key.

    The value is an array that has key value pairs, consisting of t_id=>score like this:

    Code:
    Array ( 
    ‘uid1’ => array(‘t1’=>’score1’, ‘t2’=>’score2’, ‘t3’=>’score3’, ‘t4’=>’score4’,’t5’=>’score5’,
    ‘uid2’ => array(‘t1’=>’score1’, ‘t2’=>’score2’, ‘t3’=>’score3’, ‘t4’=>’score4’,’t5’=>’score5’,
    );
    The goal is to create a leaderboard, displaying the scores of the individual tournaments and a total score for all users.
    When I try to compose the SQL for this, I get lost because of a few issues:
    • Obvisously a player registers scores for multiple tournaments, so per uid there will be up to 5 key=>pair combinations. Using a simple SELECT statement does not do the trick.
    • Not every player plays all tournaments. So not every tournament returns a valid score for all users


    In short: my SQL skills need a boost, so any pointers would be welcome.
    Many thanks in advance.

    Cheers,

    Ronald
    "Yes dear, I'll come to bed in a minute"
    R. Smit
    http://www.rsdev.nl
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by ron273
    I would like to retrieve an array that holds the uid as the key.
    can't help you with that, as i don't do perl myself

    would you like this thread moved to the perl forum?

    Originally Posted by ron273
    Using a simple SELECT statement does not do the trick
    i disagree... unless you want to generate stuff that isn't there (i.e. empty array slots for tournaments the user didn't play in)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    >not every tournament returns a valid score for all users

    So, in the final array, would you want that to register as 0 or NULL?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,015
    Rep Power
    535
    Try the following:
    Code:
    SELECT t.name,u.name,s.score
    FROM tournaments AS t
    INNER JOIN score AS s ON s.t_id=t.id
    INNER JOIN users AS u ON s.uid=u.id
    This would not return a record for a given tournament/user if nothing was entered which I expect is what you want. Otherwise, use LEFT OUTER JOIN.

    Not exactly what you are looking for, but might be close enough.

    You can get rid of score.id since the record is uniquely defined by t_id and uid.

    GROUP ON might also help you retrieve the data you desire.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    GROUP ON?

    I prefer wowcher

    Yes, drop score_id and do something like this. use the ordering to loop through and organize your array as you wish - but that's a (trivial) problem for your application level code...
    e.g.
    Code:
    SELECT u.*
         , t.*
         , ut.score
         , ut.ts
      FROM tournament t 
      JOIN user u 
      LEFT 
      JOIN user_tournament ut 
        ON ut.tournament_id = t.tournament_id 
       AND ut.user_id = u.user_id 
     ORDER 
        BY user_id
         , tournament_id;
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by cafelatte
    Code:
      FROM tournament t 
      JOIN user u 
      LEFT 
      JOIN user_tournament ut 
        ON ut.tournament_id = t.tournament_id 
       AND ut.user_id = u.user_id
    that first join was supposed to be a CROSS JOIN, yes?

    omitting both INNER and CROSS is a mysql extension, and the ON clause is optional (!) in mysql

    i would always write CROSS JOIN, as that's standard sql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    >i would always write CROSS JOIN, as that's standard sql

    And punctuation's standard English; but I don't see you using it!

    So why the double-standard?

    ;-)
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,179
    Rep Power
    4279
    Originally Posted by cafelatte
    And punctuation's standard English; but I don't see you using it!
    i do use it, quite often

    see, that last sentence included punctuation

    the thing is, my english is portable

    your SQL isn't

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378


    [pause while I think of a witty riposte...]
  18. #10
  19. No Profile Picture
    PHP-addict
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2001
    Location
    Netherlands
    Posts
    54
    Rep Power
    14
    Guys!

    Many thanks for the suggestions. I realize it is much simpler to do the formatting outside of the sql query. A simple join followed by some heavy duty formatting did the trick.

    Cheers,
    "Yes dear, I'll come to bed in a minute"
    R. Smit
    http://www.rsdev.nl

IMN logo majestic logo threadwatch logo seochat tools logo