SQL join one column to many based on condition -

-

i have 2 tables follows:

table a

docnumber  line  amount doctype  1000        1      100     3 1000        2      200     3 1001        1      300     5

table b

docnumber   debit  credit  account  1000        100     0       5410 1000        200     0       5560 1001         0      300     6790

i'm trying create select looks this:

docnumber  line  amount account 1000        1     100     5410 1000        2     200     5560 1001        1     300     6790

so, logically, i'm trying figure out way join a.amount b.debit a.doctype = 3 , join a.amount b.credit a.doctype = 5

as can see, i'm novice appreciated

assuming sql server. assuming docnumber intended play role in join well, despite not being explicitly stated in question.

you can use conditions on join want:

select a.docnumber, a.line, a.amount, b.account join b     on a.docnumber = b.docnumber        , ((a.doctype = 3 , a.amount = b.debit)             or (a.doctype = 5 , a.amount = b.credit)) order a.docnumber, a.line;

alternatively, put where clause, more clear:

select a.docnumber, a.line, a.amount, b.account join b on a.docnumber = b.docnumber (a.doctype = 3 , a.amount = b.debit) or (a.doctype = 5 , a.amount = b.credit) order a.docnumber, a.line

Comments

Post a Comment

Popular posts from this blog

c# - SelectList with Dictionary, add values to the Dictionary after it's assigned to SelectList -

-
i have following code - dictionary<string, string> mydict = new dictionary<string, string>(); mydict.add("keya", "valuea"); mydict.add("keyb", "valueb"); ienumerable<selectlistitem> myselectlist = new selectlist(mydict, "key", "value") further down in program, want add values mydict . possible? if yes, how? want - myselectlist.mydict.add("keyc", "valuec"); if you're wanting add items mydict, possible, , changes reflected in of myselectlist 's enumerations long changes made before enumeration (e.g. using .tolist() ) generated. as worked example: dictionary<string, string> mydict = new dictionary<string, string>(); mydict.add("keya", "valuea"); mydict.add("keyb", "valueb"); ienumerable<selectlistitem> myselectlist = new selectlist(mydict, "key", "value...
Read more

mysqli - Php Mysqli_fetch_assoc Error : "Warning: Illegal string offset 'name' in" -

-
this question has answer here: php mysqli returns 1 row 3 answers warning: illegal string offset 'name' in c:\xampp\htdocs\connection\index.php on line 31 1", please can me. <?php include('config.php'); $conn = mysqli_connect(db_host, db_user, db_pass, db_name); $sql = "select * users"; $result = mysqli_query($conn, $sql); $rows = mysqli_fetch_assoc($result); ?> and in body tag <?php foreach ($rows $row) { echo $row['name']; }?> you should use mysqli_fetch_all() here rows within $rows @ once. $sql = "select * users"; $result = mysqli_query($conn, $sql) or die(mysqli_error($conn); $rows = mysqli_fetch_all($result); echo "<pre>"; print_r($rows); // check result
Read more

html - Cut text on left side inside button while centering -

-
i need have button text (f.e. "add basket") fluid in width. if button width little show "add basket" must cut "basket" only. did achieve of splitting <span class="text">add basket</span> into 2 parts , reverse order them in dom <span class="text">basket</span><span class="text text-prefix">add to&nbsp;</span> and show them in correct order adding .text{ float: right; } however, text inside button must centered, not work far. here fiddle, i'd appreciate ideas: https://jsfiddle.net/5cavrrhc/ under circumstances, want avoid using js this. solution ok, here go: https://jsfiddle.net/6etabchg/4/ ok, co-worker , found solution works nicely. make button position: relative make wrapper display: flex; flex-wrap: wrap-reverse; justify-content: center; reverse order make both inner spans display: inline-block;flex-shrink: 0; make 1 span should remain or...
Read more