Problem on mysql INNER JOIN

I am trying following query to show all of my user at user page...

SELECT u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name AS team_name, sum( s.score ) AS total_score
FROM users u
INNER JOIN teams t ON u.team_id = t.id
INNER JOIN stats s ON u.id = s.user_id

I have 4 users in my users table and i want to list all but if i use INNER JOIN, its returning only 1 row. Check data structure as following...

| id | game | user_id | rank | score | dnt |
+----+------+---------+-------+------+-----+
|  1 | test | 5       | 2    | 2200  | 
+--------+----------+----------+-----+-----+
|  2 | test | 3       | 1    | 2500  | 
+--------+----------+----------+-----+-----+

teams

| id | name  | dnt |
+----+-------+-----+
|  1 | team1 |     |
+----+-------+-----+
|  2 | team2 |     |
+----+-------+-----+

users

| id | username | email | team_id |
+--开发者_StackOverflow中文版--+----------+-------+---------+
|  1 | user1    |       | 1       |
+----+----------+-------+---------+
|  1 | user2    |       | 2       |
+----+----------+-------+---------+

you need to use GROUP BY in order to split score's sum over different users, further more if you use LEFT JOIN then you'll be sure to get a row for each user instance even if it has no rows in stats table (then, using COALESCE as regilero suggested, the sum will be 0)

something like:

SELECT u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name AS team_name, sum( COALESCE(s.score) ) AS total_score
FROM users u
LEFT JOIN teams t ON u.team_id = t.id
LEFT JOIN stats s ON u.id = s.user_id
GROUP BY u.id, u.username, u.email, u.active, u.admin,
u.team_id, t.name

this is not tested