Mysql join query?

Question

ToBeCollected table

Id     Name          Amount
1      MR XXX         10000  
2      Mr YYY         15000
3      Mr ZZZ         12000

Collected Table

ID    Name        Amount
1     Mr XXX       5000
2     Mr XXX       2000
3     Mr YYY      12000
4     Mr YYY       1000

What I am looking for is this.

ID    Name   ToBeCollected    Collected     Balance
1    Mr XXX     10,000           7,000       3,000
2    Mr YYY     15,000          13,000       2,000
3    Mr ZZZ     12,000               0      12,000

Answer 1

Try this Query....

SELECT t.id,t.name,max(t.amt) as tobeCol,
       coalesce(s.amt,0) as Col,
       max(t.amt) - coalesce(s.amt,0) as Balance
FROM ToBeCollected t
LEFT JOIN (select sum(amt) as amt,name from Collected group by name) s on s.name=t.name
group by t.name,t.id;