mysql result set joining existing table

Question

is there any way to avoid using tmp table?

I am using a query with aggregate function (sum) to generate the sum of each product: the result looks like this:

product_name | sum(qty) 
product_1    | 100 
product_2    | 200 
product_5    | 300 

now i want to join the above result to another table called products. so that i will have a summary like this:

product_name | sum(qty) 
product_1    | 100 
product_2    | 200 
product_3    | 0 
product_4    | 0 
product_5    | 300 

i know 1 way of doing this is the dump the 1st query result to a temp table then join it with products table. is there a better way?

Answer 1

select products.*, sum(product.qty) 
FROM products LEFT OUTER JOIN product 
ON product.id = products.pid
GROUP BY pid