Yang Yang - 1 month ago 6
SQL Question

how can join a query result set with an 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 | 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
SELECT Product_Name, Total FROM ProductTable x
LEFT OUTER JOIN (SELECT SUM(qty) as Total, ProductID FROM InventoryTable 
    GROUP BY ProductID) y
ON x.ProductID = y.ProductID