Creating a SQL Query with two tables -

i need assistance creating query following result:

i have 2 tables accounts, lists users , how initial owe , payments, holds records of payments users have made. know how merge tables join don't know how math needed owed amount , paid amounts.

the columns in accounts consist of: id, name, account, borrowed
columns in payments consist of: id, acctid, paymentamt

i need query combine both of these tables , math show how user has paid , how user still owes initial borrowed amount.

example table data:

accounts table

id = 3, name = joe, account = business, borrowed = 100.00

payments table

id = 1, acctid = 3, paymentamt = 10.00
id = 2, acctid = 3, paymentamt = 10.00

i using ms sql in c#.

you need join , use sum , group by.

select a.name ,        a.account ,        a.borrowed  - coalesce(sum(p.paymentamt),0) [still owes],        coalesce(sum(p.paymentamt),0) paid,        a.borrowed          accounts         left join payments p        on a.id = p.acctid  group        a.name ,        a.account ,        a.borrowed 

note did left join in case no payments made. requires use of coalesce convert null sums 0

demo