>i tried to join three table and use sql statement to calculate the result (sum of quantity).
>let me list out the tree table.
>table1: stock
>field: item_name (printer,harddisk)
>
>table2: purchase
>field: item_name , quantity
>record: printer, 2
> harddisk, 4
> printer,4
> harddisk, 3
>
>table3: sales
>field: item_name, quantity
>record: harddisk, 4
> printer, 3
>
>i use sql to calculate the sum of quatity for table2 and table3
>but the result give me two times of the actual result
>my code is like this:
>
>>SELECT stock.item_name,SUM(purchase.quantity) AS quantity1,SUMsales.quantity) AS quantity2;
>>FROM database1!stock,database1!purchase,database1!sales;
>>WHERE stock.item_name=purchase.item_name;
>>AND stock.item_name=sales.item_name;
>>group by stock.item_name;
>>order by stock.item_name
>
>Can anyone tell me what's wrong and give me the right sql statement.
>thanks

SELECT stock.item_name,SUM(purchase.quantity) AS quantity1 ;
FROM stock,purchase ;
WHERE stock.item_name=purchase.item_name ;
GROUP by stock.item_name ;
ORDER by 1 ;
UNION SELECT stock.item_name,SUM(sales.quantity) AS quantity1 ;
FROM stock,sales ;
WHERE stock.item_name=sales.item_name;
GROUP by stock.item_name;