* 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]
