mysql GROUP BY question that involve 3 tables

[iamoklah]

i have 3 tables: (i have to sperate to BUY_1 & BUY_2 into 2 tables because they have different structure actually)
1. CUSTOMER
cust_id, cust_name

2. BUY_1
cust_id, buy_amount1

3. BUY_2
cust_id, buy_amount2

table CUSTOMER store customer's details, whereas BUY_1 & BUY_2 store buy records for the customer.
Now, my question is to generate a list to show the sum of (buy_amount1 + buy_amount2), which would display like:
______ _________ _______
cust_id cust_name total_buy
-------- ------------ -----------
1 user1 250
2 user2 358
-------- ------------ -----------

where total_buy=sum(buy_amount1) + sum(buy_amount2)?

I know how to write the query using GROUP BY, if that only involve table CUSTOMER and BUY_1; but how the above?pls help...

NOTE: I am using php with mysql 4.0.20

[Plebius]

Something like this might work.

SELECT total_buy as SUM(b1.buy_amount1) + SUM(b2.buy_amount2), c.cust_id, c.cust_name FROM customer c, buy_1 b1, buy_2 b2 WHERE c.cust_id = b1.cust_id AND c.cust_id = b2.cust_id GROUP BY c.cust_id

[iamoklah]

i have try it. but it shows a weird results. For example,
if there are row(s) in only BUY_1 and NULL for BUY_2 for certain cust_id, i will get the total_buy correctly that are equal sum of BUY_1.

But if there exists row(s) in both BUY_1 and BUY_2, i will get a wrong total_buy.(it shows very big number)

[Plebius]

What if you take out the SUM functions?

[r937]

what you want is a union, not a join

select c.cust_id 
     , c.cust_name 
     , sum(u.buy_amt) as total_buy 
  from customer as c
left outer
  join (
       select cust_id
            , buy_amount1 as buy_amt    
         from buy_1
       union all    
       select cust_id
            , buy_amount2   
         from buy_2
       ) as u
    on c.cust_id
     = u.cust_id

of course, if you're not on mysql 4.1, you can't do that, so you have to find a workaround

create temporary table u
( cust_id integer
, buy_amt  decimal(9,2)
);
insert into u
select cust_id
     , buy_amount1 
  from buy_1     
;            
insert into u
select cust_id
     , buy_amount2 
  from buy_2     
;            
select c.cust_id 
     , c.cust_name 
     , sum(u.buy_amt) as total_buy 
  from customer as c
left outer
  join u
    on c.cust_id
     = u.cust_id

[iamoklah]

thanks r937, i can get what i want by using the temporary table.