mos mos - 6 days ago 6
MySQL Question

mysql query for 3 tables, join or union which one is best to calculate balance?

With the following 3 tables, how to calculate the stock balance for million records in a best/faster way ?!

As my experience, using join can take less than 0.1 sec but not the final result,

using union can got the final result but it takes 1x sec...


stocktable
stid productid qty
1 1 100
2 2 200

sellstable
sellid stid qty
1 1 50
2 1 30

scraptable
scrapid stid qty
1 1 10
2 1 5
3 2 100


SELECT
a.stid,
qty,
sellid,
b.qty,
scrapid,
c.qty
FROM
stocktable AS a
LEFT JOIN sellstable AS b ON a.stid = b.stid
LEFT JOIN scraptable AS c ON a.stid = c.stid






1 100 1 50 1 10
1 100 1 50 1 5
1 100 2 30 1 10
1 100 2 30 1 5
2 200 null null 3 100


this is fast when using join for the inner query, but how to sum up the balance...

added info:


the result should be
1 5
2 100


thanks!

Answer

If you literally need all the columns in your query, along with the sum of the three qty fields, then you can do SUM(a.qty+b.qty+c.qty), but it's hard to figure exactly what you are looking for. Is there a particular key in those tables that you want to group on? Do you need sellid and scrapid at all?