1

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 | +----+----------+-------+---------+ | 1 | user1 | | 1 | +----+----------+-------+---------+ | 1 | user2 | | 2 | +----+----------+-------+---------+
Improve this question
1
  • 1
    Why do user1 and user2 both have id equal 1? Commented Jul 17, 2011 at 18:54

1 Answer 1

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

10 Comments

hello, thanks for reply, its still returning 3 rows instead of 4. We have 4 users in users table and if we do simple SELECT * from users we get 4 users but using INNER JOIN gives 3 rows/users.
and if some users have no stats a left join on stats with a COALESCE in the sum could be used.
@Daniele actually group by u.id should be enough :)
hello i have updated INNER JOIN with LEFT JOIN, and its working, do you know why?
@Karolis: restricting the number of columns in GROUP BY expression is a mysql HACK on SQL and can lead to completly wrong queries (especially fro beginners), making the group by as they should be made in SQL is better, every SELECT column which is not an aggregate should be present in the Group by.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.