i'm not really much into MySQL, but i need just one statement and I would really appreciate your help on this.
I have two tables: 'user' and 'score'
here's the structure of 'user':
| user_id | user_name |
| 1 | Paul |
| 2 | Peter |
here's the structure of 'score':
| score_id | score_user_id | score_track_id | score_points |
| 1 | 2 | 23 | 200 |
| 2 | 2 | 25 | 150 |
now I need a query that provides me some kind of highscore-list. the result should contain user_id, user_name and the sum of all scores that are related to the user: i should look like this:
| user_id | user_name | scores |
| 1 | Paul | 0 |
| 2 | Peter | 350 |
even better would be, if the result would be sorted in order of the users position in the global ranking like this:
| position | user_id | user_name | scores |
| 1 | 2 | Peter | 350 |
| 2 | 1 | Paul | 0 |
I tried the statement
SELECT user_id as current_user, user_name, SUM(SELECT score_points FROM score WHERE score_user_id = current_user) as ranking FROM user ORDER BY ranking DESC
which results in a syntax error. the main problem for me is to connect the user_id from 'user' to the score_user_id in 'score' for each row.
thank you very much for your help