mysql - How to join three tables to get Sum

Question

I have three tables: Products, Purchase, Invoice

Product table:
Producct_no     Name
1                        A
2                        B
3                        C

Purchase table:
Purchase_no    Product_no    Qty
001                    1                     81
002                    2                     150
003                    3                     10

Invoice table:
Invoice_no    Product_no    Qty
001                    1                 20
002                    2                 10
003                    3                 10

I want to get each product's purchase quantity and invoice quantity, I used following query

    SELECT PR.product_no, sum(P.qty),sum(I.qty)
    FROM products PR 
    LEFT  JOIN invoice I ON I.product_no=PR.product_no
    LEFT  JOIN purchase P ON P.product_no=PR.product_no
    group by PR.product_no

product_no    sum(P.qty)    sum(I.qty)
001                    162             160
002                    150             50
003                    10               10

EDIT: Expected results
product_no    sum(P.qty)    sum(I.qty)
001                    81             20
002                    150             10
003                    10               10

My query is giving me wrong response (sum of quantities are wrong), please help me to correct my query to get the results properly. thanks

Answer 1

I think you have a problem with GROUP BY there. I would do something like this in this case

SELECT P.Product_no, Inv.InvProdSum, Pur.PurProdSum
FROM Product P
LEFT JOIN (SELECT Product_no, SUM(Qty) AS InvProdSum
          FROM Invoice 
          GROUP BY Product_no) AS Inv
ON P.Product_no = Inv.Product_no
LEFT  JOIN (SELECT Product_no, SUM(Qty) AS PurProdSum
            FROM Purchase 
            GROUP BY Product_no) AS Pur
ON P.Product_no = Pur.Product_no

Here is SQL FIddle for that http://sqlfiddle.com/#!9/731a5/1

NOTE i add some extra value here to test how it's work...

GL!