how can join a query result set with an 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 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

Answer 2

You can do it like this

select table1.productname, virtualtable.qty
from table1 
inner join (
  select productid, qty
  from table2
  group by productid
) as virtualtable on virtualtable.productid = table1.productid

Answer 3

Perhaps the UNION syntax is what you are looking for? http://dev.mysql.com/doc/refman/5.0/en/union.html Some more information would be useful.

Answer 4

You can do multiple joins in one select query. Does that solve your problem? It's hard to tell what you are asking for.