[SOLVED] Having some trouble >.>

[m4x3vo]

Hi, I have been trying to develop a query, first let me give you some table and column info.

The orange columns correspond to eachother, and the green columns correspond to each other.

 

The table ach contains the columns id, name, and points.

The table user_ach contains the columns ach_id and user_id.

The table users contains the columns user_id and user_character.

 

I am trying to make a high score list.  I need to start with the user_id of the first user.  The query must then go through the table user_ach and search for all the ach_id's(each is its own row) that correspond to the user_id.  Then I must use those ach_id's and go into the table ach which contains the points each ach_id is worth.  I must add all the points the user has.  I have to do this for each user id.  And when I am done, I have to order the users who have the most points (descending) and limit the query to 10 results.  The table should show their character which is the column user_character and their total amount points.

 

I guess a join would make sense, and I have been playing around with code, and am yet to get something to work.  Looking for help lol.

[gassaz]

Something like this??

 

SELECT
    `users`.`user_character`
    , SUM(`ach`.`points`) AS total_points
FROM
    `user_ach`
    INNER JOIN `ach` 
        ON (`user_ach`.`ach_id` = `ach`.`id`)
    INNER JOIN `users` 
        ON (`users`.`user_id` = `user_ach`.`user_id`)
GROUP BY `users`.`user_character`
ORDER BY `users`.`user_character` ASC, SUM(`ach`.`points`) DESC LIMIT 0,10;

[m4x3vo]

Er not running it on command line or w/e.  But i dont think thats the right direction .

[Ken2k7]

Doesn't matter. Run it through PHP's mysql_query if you want or via phpMyAdmin.

[m4x3vo]

Something like this??

 

SELECT
    `users`.`user_character`
    , SUM(`ach`.`points`) AS total_points
FROM
    `user_ach`
    INNER JOIN `ach` 
        ON (`user_ach`.`ach_id` = `ach`.`id`)
    INNER JOIN `users` 
        ON (`users`.`user_id` = `user_ach`.`user_id`)
GROUP BY `users`.`user_character`
ORDER BY `users`.`user_character` ASC, SUM(`ach`.`points`) DESC LIMIT 0,10;

 

WOW that worked exactly, thanks so much!