JOIN query

[anand01]

hi All,
I have two tables
table-1:customeramount
table-2:customerpurchaseamount
table1 have customers bonus amount and table 2 i have customers purchase amount now i need to calculate balance bonus amount i tried with following query but It shows unexpected result , could any one guide me..

    SELECT CM.`custid`,
 SUM(CM.`amount`) - SUM(CP.`Amount`) as balance 
      FROM 
`customeramount` as CM JOIN customerpurchaseamount as CP 
       ON 
CM.`custid`= CP.`CustomerID` 
GROUP BY CM.`custid`

[smantscheff]

Show some test data and the unexpected as well as the expected results.

[anand01]

Thanks for your replay smantscheff,
my test data
table 1 : customeramount

custid             date           amount
1335279294867   2012-04-28          5
1335518740839   2012-04-28         124
1335357566946   2012-04-28         124
1335279294867   2012-05-17         10

Table2: customerpurchaseamount

TransactionID       CustomerID        Amount      Date
1                   1335518740839     5           2012-05-17
2                   1335518740839     10          2012-05-31

the result I am getting

1335518740839   233

But I need result set as

  1335279294867             5
  1335518740839           109
  1335357566946           124
  1335279294867           10

[smantscheff]

/*    
drop table if exists customeramount;
create customeramount (custid varchar(20), date date, amount integer);
insert into customeramount values (
('1335279294867','2012-04-28','5'),
('1335518740839','2012-04-28','124'),
('1335357566946','2012-04-28','124'),
('1335279294867','2012-05-17','10');


drop table if exists customerpurchaseamount;
create table customerpurchaseamount (TransactionID integer, CustomerID varchar(20), Amount integer, Date date);
insert into customerpurchaseamount values
('1','1335518740839','5','2012-05-17'),
('2','1335518740839','10','2012-05-31');
*/
select custid, ifnull(r,amount) as result
from
(

select a.custid, 
a.amount, a.amount - (
  select sum(b.Amount) 
  from customerpurchaseamount b 
  where a.custid=b.CustomerID
) as r
from customeramount a
) x

[anand01]

thanks smantscheff,
Its working perfectly,You spent valuable to slove my query ,
thanks again