Odtnhj

Let's say I have two tables, one storing accounts, one storing transactions :

id acct_name opening_bal
1 checking 1029.99
2 savings 2002.19
... 

And

id date amount from_acct to_acct
...
99 2018-01-21 12.15 1 2
100 2018-01-21 9.99 4 1
101 2018-01-23 10.01 5 2
...

For example, row 99 in the transactions table is saying that 12.15 was transfered from checking to savings on 21 Jan 2018.

I would like to write a query that returns all accounts together with their balances on a given date (like today or 12 Oct 2018, etc), something like this :

acct balance
checking 1599.21
savings 2221.99
...

How would I write such a query?

Edit: Here's a solution, which is close enough to what I want (it just has an additional id column). You can replace CURDATE() with an arbitrary date to get the corresponding table of balances on that date.

SELECT id, acct_name, opening_bal+amt-amt2 as balance FROM accounts 
 INNER JOIN 
 (SELECT to_acct, sum(amount) AS amt 
 FROM transactions
 WHERE date <= CURDATE() 
 GROUP BY to_acct) as T2 
 ON accounts.id=T2.to_acct 
 INNER JOIN 
 (SELECT from_acct, sum(amount) AS amt2 
 FROM transactions 
 WHERE date <= CURDATE()
 GROUP BY from_acct) as T3 
 ON T2.to_acct = T3.from_acct 
 ;

Something like this. If you'd provided more data, answer could be more precise. Table1 is your first table, Table2 is the second.

select acct_name as acct,
opening_bal - t1.put_money + t2.get_money as balance
from Table1
left join (select from_acct, ifnull(sum(amount),0) as put_money from Table2 group by from_acct) t1
on t1.from_acct = Table1.id
left join (select to_acct, ifnull(sum(amount),0) as get_money from Table2 group by to_acct) t2
on t2.to_acct = Table1.id;