mysql - SQL - "merge" 3 tables with JOIN -

-

i want select users table depending 2 relation tables

the structure:

[user]  uid | firstname | lastname | ... --------------------------------- 482 | usera     | usera    | ... 885 | userb     | userb    | ... 405 | userc     | userc    | ... 385 | userd     | userd    | ...  [news_info]  uid_local   | uid_foreign -------------------------------- 125         | 482        100         | 405         [news_add]  uid_local   | uid_foreign -------------------------------- 125         | 885    105         | 385

now want select usera , userb via uid_local -> 125, [news_info] , [news_add]

select    nnfo.uid_local,    user.*  user   join news_info nnfo     on nnfo.uid_foreign = user.uid nnfo.uid_local = 125

result = usera // works

select    nadd.uid_local,   user.*  user   join news_add nadd     on nadd.uid_foreign = user.uid nadd.uid_local = 125

result = userb // works

now "merge" sql statement one...to usera , userb

select    nnfo.uid_local,    nadd.uid_local,   user.*  user   join news_info nnfo     on nnfo.uid_foreign = user.uid   join news_add nadd     on nadd.uid_foreign = user.uid nnfo.uid_local = 125 , nadd.uid_local = 125

result = empty // no errors.....

what wrong...i tryed other statements no result :(

when merging results of queries, there 2 different approaches:

  • union
  • joins

to know 1 choose have know whether there's technical link between tables in 2 queries wish merge. in case, there's functional link (nnfo.uid_local = 125 , nadd.uid_local = 125) it's not enough join these 2 universes. still could result 1 select, think final result unclear , somehow not in spirit of sql provides.

i'd go towards union solution:

select    nnfo.uid_local,    user.*  user   join news_info nnfo     on nnfo.uid_foreign = user.uid nnfo.uid_local = 125  union select    nadd.uid_local,   user.*  user   join news_add nadd     on nadd.uid_foreign = user.uid nadd.uid_local = 125

edit: see davek's result implements union all instead of union. assumed wished return usera once if happened appear in both independant queries, that's why used simple union operator vs union all return duplicate results well.


Comments

Post a Comment

Popular posts from this blog

c# - must be a non-abstract type with a public parameterless constructor in redis -

-
when save object, got below error must non-abstract type public parameterless constructor in order use parameter 't' in generic type or method 'servicestack.redis.redisclient.store<t>(t) redisclass.getinstnace().store(msg); // error here redisclass.getinstnace().save(); as third party's class, can not edit how save object? could create wrapper around third party object call constructor, store wrapper? e.g. public class mywrapper { public thirdpartyobject thirdpartyinstance { get; set; } public mywrapper() { thirdpartyinstance = new thirdpartyobject("constructors"); } }
Read more

c# - ReportViewer control - axd url -

-
i've embedded microsoft.reporting.webforms.reportviewer control in aspx. site works on mysite.com. when try exmple export report, there new black window opened address mysite.com/reserved.reportviewerwebcontrol.axd... it's working fine. but.. pass axd via proxy. mean, i'd force export use url myproxy.mysite.com/reserved.reportviewerwebcontrol.axd... redirect request mysite.com/reserved.reportviewerwebcontrol.axd... i've noticed, link build within js (that inlcuded in axd): exportreport: function(format) { if (this.exporturlbase == null) return false; window.open(this.exporturlbase + encodeuricomponent(format), "_blank"); return true; }, the url axd stored in exporturlbase . how can change proxy serwer url? it possible modify exporturlbase field in javascript. report stored in placeholder somewhere on page, once found can create call _getinternalviewer() function on gain access exporturlbase. have @ following code: ...
Read more

time series - R growth rate calculation week over week on daily timeseries data -

-
i'm trying calculate w/w growth rates entirely in r. use excel, or preprocess ruby, that's not point. data.frame example date gpv type 1 2013-04-01 12900 office 2 2013-04-02 16232 office 3 2013-04-03 10035 office i want factored 'type' , need wrap date type column weeks. , calculate week on week growth. i think need ddply group week - custom function determines if date in given week or not? then, after that, use diff , find growth b/w weeks divided previous week. then i'll plot week/week growths, or use data.frame export it. this closed had same useful ideas. update: answer ggplot: all same below, use instead of plot ggplot(data.frame(week=seq(length(gr)), gr), aes(x=week,y=gr*100)) + geom_point() + geom_smooth(method='loess') + coord_cartesian(xlim = c(.95, 10.05)) + scale_x_discrete() + ggtitle('week on week growth rate, apr 1') + ylab('growth rate %') (old, correct answer using plot) w...
Read more