Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Sep 2007
    Posts
    9

    Unanswered: Able to do in one query?

    Hi all, I have two tables

    Level Table
    ---------------
    Level_ID -- Level_Name
    1-----------Beginner
    2-----------Intermediate
    3-----------Semi Pro
    4-----------Pro
    5-----------God



    Score Table
    ---------------
    User_ID -- Level_ID -- Score -- Scored_Time
    1001 --------1----------57------10:00:05
    1001 --------1----------18------12:00:05
    1001 --------2----------86------13:20:05
    1001 --------2----------104-----11:00:11
    1001 --------3----------56------04:23:58
    1001 --------3----------49------05:00:05
    1001 --------3----------81------09:20:41
    1001 --------4----------213------21:10:28
    1001 --------4----------159------21:14:05
    1001 --------4----------198------21:15:09



    Ok, now how can I get a score board for user id 1001 like below using one query?

    Score Result
    ---------------
    Level Name -- Last Score -- Best Score
    Beginner-----------18------------57
    Intermediate ------86-----------104
    Semi Pro-----------81----------81
    Pro ---------------198----------213
    God---------------NULL----------NULL



    Any idea?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    USER_ID is a reserved word!! I advise you change it
    Also, Scored_Time... What is the datatype of that?
    How do you define the "Last Score"?
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    9

    Thanks for reply

    User ID just a example actually.

    score time is YYY-MM-DD HH:mm:ss

    Last score means latest score saved, like last played score.

    Thanks

  4. #4
    Join Date
    Sep 2007
    Posts
    9
    Also, for your info. My hosting's MYSQL version is 4.0.20...

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    furthermore, what is the primary key on the score table?
    Here's something to get you started
    Code:
    SELECT l.level_name
         , Max(s.score) As 'Best Score'
    FROM   levels l
     LEFT
      JOIN scores s
        ON s.level_id = l.level_id
    GROUP
        BY l.level_name
         , l.level_id
    ORDER
        BY l.level_id ASC
    P.S. Moved to mySQL topic
    George
    Home | Blog

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I reckon this should do the job... Let me know!
    Code:
    SELECT l.level_name
         , s.score       As [Latest Score]
         , Max(s2.score) As [Best Score]
    FROM   levels l
     LEFT
      JOIN scores s
        ON s.level_id = l.level_id
       AND s.score_datetime = (SELECT Max(score_datetime) FROM scores WHERE s.level_id = level_id)
     LEFT
      JOIN scores s2
        ON s2.level_id = l.level_id
    GROUP
        BY l.level_name
         , l.level_id
         , s.score
    ORDER
        BY l.level_id ASC
    George
    Home | Blog

  7. #7
    Join Date
    Sep 2007
    Posts
    9
    primary key for score table is score_id.

    Yeah...you rock! I am too stupid on SQL. How about Last Score? Need to do any query?

  8. #8
    Join Date
    Sep 2007
    Posts
    9
    Tested on your reckon query. Error found. are you using sub query? I believe i read somewhere below MYSQL version 4.1 no support sub queries...is that true?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I thought 4.1 was where subqueries were introduced...
    What error(s) are you getting?
    EDIT: So yes, you will not be able to run this query, damnit...
    I'll get back to you...
    George
    Home | Blog

  10. #10
    Join Date
    Sep 2007
    Posts
    9
    Haha, sorry for your time.


    MySQL said:
    #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 'SELECT Max( logtime )
    FROM gb_user_score
    WHERE s.level_id = '

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by kmf165
    ...WHERE s.level_id = '
    I never used that
    George
    Home | Blog

  12. #12
    Join Date
    Sep 2007
    Posts
    9
    the whole query I use :



    SELECT l.level_name, s.score As 'Latest Score', Max(s2.score) As 'Best Score'
    FROM gb_level AS l
    LEFT JOIN gb_user_score AS s ON s.level_id = l.level_id AND s.logtime = (SELECT Max(logtime) FROM gb_user_score WHERE s.level_id = level_id)

    LEFT JOIN gb_user_score s2 ON s2.level_id = l.level_id
    GROUP BY l.level_name, l.level_id, s.score
    ORDER BY l.level_id ASC



    **But I was trying to get score of user id 1001

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    george, be careful -- you cannot ORDER BY a column that isn't in the SELECT clause of a GROUP BY query

    if the query does not have a GROUP BY clause, then you can

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I did not know that! It just appeared to work...
    I don't see why I can't order by something that's not in the SELECT clause; don't suppose you fancy providing an example?
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that sounds like work, which requires time, of which there is a very short supply

    especially since mysql will go ahead and run invalid queries anyway

    for the time being, please just keep this in mind: if you want to ORDER BY something, make sure it's in the SELECT clause in all cases (you're much safer that way)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •