sql server - Stuck with sql joins -


i newbie sql. have these tables:

table individual:

individualid  individualname   ind1          mark ind2          paul ind3          spencer ind4          mike ind5          hilary

table agent:

agentid     indid(foreignkey) agent1      ind1 agent1      ind2 agent2      ind1 agent3      ind4

transaction table (transaction can made via agent or individual himself):

tranid   indid(foreignkey/never null)  agentid(foreignkey)  tranamount tran1    ind1                          agent1               $100 tran2    ind1                          null                 $500 tran3    ind3                          null                 $200 tran4    ind4                          agent3               $50 tran5    ind4                          null                 $30

result should like: (if transaction associated agent, display in same line else display on separate line)

individualid  individualname  agentid    tranid    tranamount ind1          mark            agent1     tran1     $100 ind1          mark            agent2     null      null ind1          mark            null       tran2     $500 ind2          paul            agent1     null      null ind3          spencer         null       tran3     $200 ind4          mike            agent3     tran4     $50 ind4          mike            null       tran5     $30 ind5          hilary          null       null      null

as per comments, used following query: 

select distinct individualid, individualname, tranid, agent.agentid, amount  individual  left join transactions  on   individual.individualid = transactions.indid  left join agent  on   transactions.agentid = agent.agentid

which gave below results not correct:

individualid    individualname  tranid  agentid amount    ind1            mark            tran1   agent1  100   ind1            mark            tran2   null    500   ind2            paul            null    null    null   ind3            spencer         tran3   null    200  ind4            mike            tran4   agent3  50  ind4            mike            tran5   null    30   ind5            hilary          null    null    null

try this:

select distinct individualid,  individualname, agent.agentid, tranid, amount  individual left join agent on individual.individualid = agent.indid  left join transactions   on individual.individualid = transactions.indid  ,  transactions.agentid = agent.agentid   union      select distinct individualid, name,  tranid,null agentid, amount  individual left join transactions   on individual.individualid = transactions.indid  , transactions.agentid null

Comments

Post a Comment

Popular posts from this blog

php - failed to open stream: HTTP request failed! HTTP/1.0 400 Bad Request -

this code i'm using find latitude , longitude of place: function coords($address){ echo $url="http://maps.googleapis.com/maps/api/geocode/json?address=".$address; $json = file_get_contents(urlencode($url)); $data = json_decode($json, true); print_r($data['results'][0]['geometry']['location']['lat']); print_r($data['results'][0]['geometry']['location']['lng']); } however returns warning: failed open stream: http request failed! http/1.0 400 bad request if use above code in simple procedure works fine not in function. note: have tried curl_init()... method produced same result? instead of encoding whole url encode address part. following code work fine $add='jamshoro phase 2'; $url="http://maps.googleapis.com/maps/api/geocode/json?address=".urlencode($add); $json = file_get_contents($url); $data = json_decode($json, true); print_r($data['r
Read more

java - How to filter a backspace keyboard input -

i have code gives message when key number pressed: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochararray().length - 1]; if (!(ch >= '0' && ch <= '9')) { system.out.println("the char entered not number"); t.consume(); } } }); now if press on wrong button , press backspace remove it, error message. how can add backspace input if statement? i have found answer: txtvalueofclause.addeventfilter(keyevent.key_typed, new eventhandler<keyevent>() { @override public void handle(keyevent t) { char ar[] = t.getcharacter().tochararray(); char ch = ar[t.getcharacter().tochar
Read more

java - Show Soft Keyboard when EditText Appears -

i have code in android app, alert dialog , keyboard, , when dialog shown want keyboard appear. not , not sure why? here code: final alertdialog.builder alert = new alertdialog.builder(this); alert.settitle("title"); alert.setmessage("message"); final edittext input = new edittext(this); input.setinputtype(inputtype.type_class_number); alert.setview(input); input.setonfocuschangelistener(new view.onfocuschangelistener() { @override public void onfocuschange(view v, boolean hasfocus) { if (hasfocus) { log.i(tag, "in focus"); inputmethodmanager inputmethodmanager = (inputmethodmanager) getsystemservice(context.input_method_service); inputmethodmanager.showsoftinput(input, inputmethodmanager.show_implicit); } } }); but keyboard not appear though edit text in focus, , tell appear why, doesn't come up? thanks in advance.
Read more