Problem on mysql INNER JOIN -

Problem on mysql INNER JOIN -

i trying next query show of user @ user page...

select u.id, u.username, u.email, u.active, u.admin, u.team_id, t.name team_name, sum( s.score ) total_score users u inner bring together teams t on u.team_id = t.id inner bring together stats s on u.id = s.user_id

i have 4 users in users table , want list if utilize inner join, returning 1 row. check info construction 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 | +----+----------+-------+---------+ | 1 | user1 | | 1 | +----+----------+-------+---------+ | 1 | user2 | | 2 | +----+----------+-------+---------+

you need utilize group by in order split score's sum on different users, farther more if utilize left join you'll sure row each user instance if has no rows in stats table (then, using coalesce regilero suggested, sum 0)

something like:

select u.id, u.username, u.email, u.active, u.admin, u.team_id, t.name team_name, sum( coalesce(s.score) ) total_score users u left bring together teams t on u.team_id = t.id left bring together stats s on u.id = s.user_id grouping u.id, u.username, u.email, u.active, u.admin, u.team_id, t.name

this not tested

mysql