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

Comments

Post a Comment

Popular posts from this blog

c# - Move local mssql database to webhosted MYSQL -

c# - Move local mssql database to webhosted MYSQL - i'm dektop developer , don't know much how webhosting work... please help me here. currently app works mssql database installed locally on same machine. need go wider , allow multiple apps work same database on internet. have webhosting cpanel in , mysql database. please tell me how can access tables in mysql database computers? select , update records in table. have implement functionality using php create such requests? please advise.. exporting raw ms-sql dumps not work if seek import mysql because there differences in syntax. there commercial programs available help migrate data, otherwise might improve code php programme export mssql mysql. but question sounds more you're asking how access databases remotely. . php can natively. when create link database specify host. example: $databaseconnection = new mysqli('username','password','host_ip_address'); if you're...
Read more

java - How to pass parameters between Request-Scoped Controllers? -

java - How to pass parameters between Request-Scoped Controllers? - i have simple jsf-page , want pass parameter page1 page2. every page has own controller in request scope. the 2 controllers this: controller1: @component("requestcontroller1") @scope("request") public class requestcontroller1 { private list<product> products; private product selectedproduct; @postconstruct public void init() { //load products db } public void setproducts(list<product> products) { this.products = products; } public list<product> getproducts() { homecoming products; } public void setselectedproduct(product selectedproduct) { this.selectedproduct = selectedproduct; } public product getselectedproduct() { homecoming selectedproduct; } controller2: @component("requestcontroller2") @scope("request") public class requestcontroller2 { private long selectedproductid; @postconstruct public void init() ...
Read more

string - what is the difference between "some" == "some\0" and strcmp("some","some\0") in c++? -

string - what is the difference between "some" == "some\0" and strcmp("some","some\0") in c++? - what difference between "some" == "some\0" , strcmp("some","some\0") in c++? why if("some" == "some\0") returns false , if(!strcmp("some","some\0")) returns true ? see next diagram. shows 2 strings in memory, content in box, beside box you'll see address of each one. when you're doing if("some" == "some\0") comparing addresses. translated if (0xdeadbeef == 0x0badcafe) false. when utilize strcmp , compare content of each box until reach \0 in each of them. that's why sec test returns true. if alter first test if("some" == "some") compiler may potentially see same strings , store them once. means test transform if (0x0badcafe == 0x0badcafe) true. c++ string comparison
Read more