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

html5 - What is breaking my page when printing? -

-
Image
i have questioner , don't know why when printing it's creating break before first question. live demo (link) i able resolve issue removing fieldset container had wrapped in.. having fieldset element causing of fields grouped together, , since not fit on first page, being pushed second page. removing fieldset element, able firefox print first 2 questions on page 1 identically chrome. it seems not new problem firefox , fieldset not playing nice found old bugzilla ticket 2008 has persisted , still being commented on in 2014 (link) if retaining fieldset important, found solution else came here (link) <script type='text/javascript'> $(window).bind('beforeprint', function(){ $('fieldset').each( function(item) { $(this).replacewith($('<div class="fieldset">' + this.innerhtml + '</div>')); } ) }); $(window).bind(
Read more

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

ajax - PHP/JSON Login script (Twitter style) not setting sessions -

-
i'm having trouble getting twitter style login work properly, reason gets stuck at session_register("useremail"); session_register("userpass"); and doesn't redirect after it. if enter wrong account data, works , appropriate error message pops up. this full code: <?php if(empty($_post['email']) || empty($_post['password'])) { die(msg(0,"all fields required")); } if(!(preg_match("/^[\.a-z0-9_\-\+]+[@][a-z0-9_\-]+([.][a-z0-9_\-]+)+[a-z]{1,4}$/", $_post['email']))) die(msg(0,"you haven't provided valid e-mail")); $host="localhost"; $username="root"; $password="x"; $db_name="x"; $tbl_name="x"; mysql_connect("$host", "$username", "$password")or die("cannot connect"); mysql_select_db("$db_name")or die("cannot select db"); $useremail=$_post['email']; $userpass=
Read more