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