Sum with inner join in PHP

Question

I'm working with PHP and MySQL, and I need to SUM the total amount of products joining 3 tables:

order_products: (There are multiple order products with the same name but different amounts in the table)

order_id        (int)
product_name    (varchar)
product_amount  (int)

orders:

order_id       (int)
order_date     (varchar)
order_status   (varchar)

supplier:

product_name    (varchar)
product_amount  (int)

So, I want to show how many products I sold and status is shipped and how many I ordered from the supplier in one single row. Any of two examples below will help me to achieve my goal.

Like:

Product Name     (sum order_products)      (sum supplier)  Order status

first product           300                  2500          Shipped_Only
second product          50                   400           Shipped_Only
third product           10                   600           Shipped_Only


Product Name     (sum order_products)      (sum supplier)  Order status

first product           2200                 2500          Not_Shipped
second product          400                  400           Not_Shipped
third product           590                  600           Not_Shipped

Are there any examples or other help that I can get to do this?

Edit: Sample Data goes like this

order_products:

order_id       product_name     product_amount

255               product 1         200
256               product 1         100
257               product 2         50
258               product 3         10

orders: order_id order_date order_status

255               09.05.2018         Shipped
256               09.05.2018         Shipped
257               10.05.2018         Not_Shipped
258               10.05.2018         Not_Shipped

supplier: product_name product_amount

product 1         2500        
product 2         400   
product 3         600    

Answer 1

You should use a join on the aggregated subselect.

SELECT t1.product_name, t1.sum_order_products, t2.supplier_sum, t1.order_status 
FROM (
    SELECT op.product_name, SUM(op.product_amount) sum_order_products, o.order_status
    FROM order_products op
    INNER JOIN orders o ON op.order_id = o.order_id 
    WHERE o.order_status = 'Shipped'
    GROUP BY op.product_name, o.order_status
) t1 
LEFT JOIN (
    SELECT s.product_name, SUM(s.product_amount) supplier_sum
    FROM supplier s 
    GROUP BY s.product_name
) t2 ON t1.product_name = t2.product_name 
ORDER BY t1.order_status, t1.product_name