php - MYSQL Joins in Select query -

-

i writing database manage sport league table needs separate tabe hold details of occasional points variations fall outside normal logic of league table calculation.

the 2 tables have are

    create table if not exists `fixtures` (   `id` int(6) unsigned not null auto_increment,   `comp_id` int(6) not null,   `date` date default null,   `time` time default null,   `hteam_id` int(6) default null,   `ateam_id` int(6) default null,   `hscore1` int(6) default null,   `ascore1` int(6) default null,   `hscore2` int(6) default null,   `ascore2` int(6) default null,   `hbonus` int(6) not null,   `abonus` int(6) not null,   `played` varchar(2) not null,   primary key (`id`) ) engine=myisam  default charset=latin1 auto_increment=30 ;  create table if not exists `pointsadjust` (   `id` int(6) unsigned not null auto_increment,   `fix_id` int(6) default null,   `team_id` int(6) default null,   `value` int(6) default null,   `reason` varchar(75) character set latin1 collate latin1_general_ci default null,   primary key (`id`) ) engine=myisam  default charset=latin1 auto_increment=3 ;

the query using pull league table is

select   tname team, sum(p) p,sum(w) w,sum(d) d,sum(l) l,   sum(f) f,sum(a) a,sum(sd) sd,sum(bp) bp,sum(pts)+sum(bp) pts, sum(((pts+bp)*10000)+(w*100)+(sd*10)+((f/p)*1)) rank from(   select     hteam_id team,     1 p,     if(hscore1 > ascore1,1,0) w,     if(hscore1 = ascore1,1,0) d,     if(hscore1 < ascore1,1,0) l,     hscore1 f,     ascore1 a,     hscore1-ascore1 sd,     hbonus bp,     case when hscore1 > ascore1 2 when hscore1 = ascore1 1 else 0 end pts   fixtures comp_id = '1' , played = 'n'   union   select     ateam_id,     1,     if(hscore1 < ascore1,1,0),     if(hscore1 = ascore1,1,0),     if(hscore1 > ascore1,1,0),     ascore1,     hscore1,     ascore1-hscore1,     abonus,     case when hscore1 < ascore1 2 when hscore1 = ascore1 1 else 0 end   fixtures comp_id = '1' , played = 'n' ) tot join teams t on tot.team=t.id group team order rank desc

i struggling create second join bring in data points adjust table make adjustments league table.

can please?

the best way replace block of code of sub-query "tot" one, , point new column "pts_adjst" (witch holds adjusted punctuation) instead of "pts" (the "vanilla" punctuation):

sql fiddle (example): http://sqlfiddle.com/#!9/c7a3c/14

select #tname team, team teamid, sum(p) p,sum(w) w,sum(d) d,sum(l) l, sum(f) f,sum(a) a,sum(sd) sd,sum(bp) bp,sum(pts_adjst)+sum(bp) pts, sum(((pts_adjst+bp)*10000)+(w*100)+(sd*10)+((f/p)*1)) rank from(select tot.*, case when pointsadjust.value not null pointsadjust.value else pts end pts_adjst, pointsadjust.reason ( select  hteam_id team,  id fix,  1 p,  if(hscore1 > ascore1,1,0) w,  if(hscore1 = ascore1,1,0) d,  if(hscore1 < ascore1,1,0) l,  hscore1 f,  ascore1 a,  hscore1-ascore1 sd,  hbonus bp,  case when hscore1 > ascore1 2 when hscore1 = ascore1 1 else 0 end pts fixtures comp_id = '1' , played = 'n' union select  ateam_id,  id fix,  1,  if(hscore1 < ascore1,1,0),  if(hscore1 = ascore1,1,0),  if(hscore1 > ascore1,1,0),  ascore1,  hscore1,  ascore1-hscore1,  abonus,  case when hscore1 < ascore1 2 when hscore1 = ascore1 1 else 0 end  fixtures comp_id = '1' , played = 'n' ) tot left join pointsadjust  on tot.team = pointsadjust.team_id , tot.fix = pointsadjust.fix_id) tot #join teams t on tot.team=t.id group team order rank desc

Comments

Post a Comment

Popular posts from this blog

python - No exponential form of the z-axis in matplotlib-3D-plots -

-
Image
i have similar problem described in how prevent numbers being changed exponential form in python matplotlib figure : i don't want (in special case) weird scientific formatting of axis. problem different have problem @ z -axis. 2-d plots can use ax.get_yaxis().get_major_formatter().set_useoffset(false) . , there no function ax.get_zaxis() what use format z -axis same way? edit: example: from mpl_toolkits.mplot3d import axes3d import numpy np import sys import matplotlib import matplotlib.pyplot pyplot def func(xi, ti): res = 10e3 + np.cos(ti) * np.sin(xi) return res if __name__ == '__main__': timespacing = 20 timestart = 0 timeend = 1 time = np.linspace(timestart, timeend, timespacing) widthspacing = 50 widthstart = 0 widthend = 3 width = np.linspace(widthstart, widthend, widthspacing) reslist = [] matplotlib.rcparams['legend.fontsize'] = 10 fig = pyplot.figure() ax = fig.gca(projection = '3d...
Read more

php - Best Light server (Linux + Web server + Database) for Raspberry Pi -

-
i install web server database on raspberry pi (little computer). computer has 1gb ram. i want know best combination: linux distribution , web server , dbms run local server multiple users minimal latency, use php on server. , best settings performance , not have bugs (memory usage, disable plugin, disable service, etc)? i thought light debian , lighttpd server , sqlite database. solution? i think lighttpd + sqlite great choice. linux distro, debian @ centos or tiny core linux , although i'm not sure of compatibility pi. obviously, can't go wrong raspbian if want use in production , more stable performance, few more pi's , set them in cluster .
Read more

c# - "Newtonsoft.Json.JsonSerializationException unable to find constructor to use for types" error when deserializing class -

-
i developing app in xamarin android. have splash screen serialize class , pass mainactivity using intent . when try deserialize in mainactivity error message : "serializationexception unable find constructor use types" serialization : void loaddata() { currency currency = new currency(this); intent = new intent(this, typeof(mainactivity)); intent.putextra("currency", newtonsoft.json.jsonconvert.serializeobject(currency)); startactivity(intent); } deserialization : cur = newtonsoft.json.jsonconvert.deserializeobject<currency> (intent.getstringextra("currency")); // error here class constructor : public currency(context context) { thiscontext = context; } i began experiencing problem after added parameter context context constructor. has caused this? how can solve it? possible serialize/deserialize class has parameters? you need have empty constructor in currency class in order deserialize it. ...
Read more