Mysql query for sum two table data by product wise

If I use the below code the sum of sale and purchase product wise perfect but 5 NO product missing because no purchase only sale but I want to show all product. what-ever purchase or sale are zero.

SELECT *
FROM (
    SELECT product_id, SUM(quantity) AS sale
    FROM order_item group by product_id
) P
JOIN (
    SELECT product_id, SUM(quantity) AS purchase
    FROM pur_item
    group by product_id
) S
JOIN (
    SELECT product_id as Pid
    FROM product GROUP BY Pid
) I ON I.Pid = P.product_id AND S.product_id = P.product_id

If is use below code then result is like below. I don’t know sum of sale and purchase is not perfect.

select p.product_id, sum(s.quantity) sale, sum(c.quantity) purchase
from product p
left join pur_item c on c.product_id = p.product_id
left join order_item s on s.product_id = p.product_id
where c.quantity is not null or s.quantity is not null
group by p.product_id

I want result for all item sum of product wise data what ever sale or purchase made.

Answer

You are taking the correct approach in your first query but your JOIN conditions are wrong and you need to use LEFT JOINs instead of JOIN to get products which have no purchases or no sales:

SELECT I.product_id, 
       COALESCE(S.sale, 0) AS sale, 
       COALESCE(P.purchase, 0) AS purchase
FROM Product I
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS sale
    FROM order_item 
    GROUP BY product_id
) S ON S.product_id = I.product_id
LEFT JOIN (
    SELECT product_id, SUM(quantity) AS purchase
    FROM pur_item
    GROUP BY product_id
) P ON P.product_id = I.product_id

We also use COALESCE to convert NULL values (when a product has no purchases or sales) to 0.