mysql result set joining existing table
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 开发者_如何学Go| 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?
select products.*, sum(product.qty)
FROM products LEFT OUTER JOIN product
ON product.id = products.pid
GROUP BY pid
精彩评论