* zhu xiaofeng
> Sent: Thursday, October 09, 2003 4:45 AM
An old question, but no replies...
> There is a question:
> Two tables:
>table_out: ( fields ) product_name,out_count , out_date
>table_in: ( fields ) product_name,in_count , in_date
> I want to create a table that show the remain of product.
Try something like this:
# Make a temporary table with one line for each product
CREATE TEMPORARY TABLE tmp_in
SELECT product_name,sum(in_count) AS in_count
FROM table_in
GROUP BY product_name;
# Do the same for the other table
CREATE TEMPORARY TABLE tmp_out
SELECT product_name,sum(out_count) AS out_count
FROM table_out
GROUP BY product_name;
# Create the new table "table_new" by joining the temporary tables:
CREATE TABLE table_new
SELECT tmp_in.product_name,SUM(in_count) - SUM(out_count) AS new_count
FROM tmp_in
LEFT JOIN tmp_out USING(product_name)
GROUP BY tmp_in.product_name
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
