Error in posting products sold and incorrect result of available stock

These are the most important tables to get the desired result:

Records: tbl_order

id_order	id_enterprise	id_branch_office	code_unique	title_product	model	size	color	quantity
1	null	1	HOLA	null	null	X	null	10
2	null	1	HOLA	null	null	XL	null	3
3	null	1	HOLA	null	null	null	RED	3
4	1	null	HOLA	null	null	null	RED	3

Records: tbl_stock_product

id_stock_product	id_enterprise	id_branch_office	code_unique	title_product	model	size	color	item_total
1	null	1	HOLA	null	null	X	null	100
2	null	1	HOLA	null	null	X	null	1000
3	null	1	HOLA	null	null	XL	null	500
4	null	1	HOLA	null	null	null	RED	10
4	null	1	HOL	MDLX1	null	null	null	300

It should be noted that the model, size, color columns can be with data or empty depending on the type of product. What is required is the unique code of the product and to whom it belongs, it can be just a company(id_enterprise) or branch(id_branch_office).

With the following query I have achieved almost everything, the only problem is total_sales and stock:

select office_establishment,
       office_tradename,
       code_unique,
       model,
       size,
       color,
       sum(item_total) as item_total,
       sum(quantity)   as quantity,
       sum(stock)      as stock
from (
         SELECT bo.establishment                          AS office_establishment,
                bo.tradename                              AS office_tradename,
                sp.code_unique,
                sp.model,
                sp.size,
                sp.color,
                item_total,
                SUM(ifnull(odr.quantity, 0))              AS quantity,
                item_total - sum(ifnull(odr.quantity, 0)) as stock
         FROM tbl_stock_product sp
                  LEFT JOIN tbl_access ac
                            ON sp.id_enterprise = ac.id_enterprise OR sp.id_branch_office = ac.id_branch_office
                  LEFT JOIN tbl_enterprise ent ON sp.id_enterprise = ent.id_enterprise
                  LEFT JOIN tbl_branch_office bo ON sp.id_branch_office = bo.id_branch_office
                  LEFT JOIN tbl_order odr on odr.id_product = sp.id_stock_product
         WHERE ac.id_user = 2
         GROUP BY ent.establishment, bo.establishment, bo.tradename, sp.code_unique, sp.model, sp.size, sp.color,
                  sp.id_stock_product, sp.item_total
     ) t
group by office_establishment, office_tradename, code_unique, model, size, color

The problems:

• t.quantity AS total_sales:

  • I am subtracting the same sales from the other results, despite having a different size, color or model.
  • When there are no sales records in the tbl_order table, it does not show anything, ideally it would show 0.

• SUM(t.item_total) - t.quantity AS stock

  • When there are no sales records in the tbl_order table, it does not show anything,Ideally, it would be to re-display the total available products by saying: SUM(t.item_total) AS item_total

And what is affecting this problem is the following conditions passed to the tbl_order table:

                LEFT JOIN tbl_order odr
                    ON sp.code_unique = odr.code_unique
                    AND (sp.id_enterprise = odr.id_enterprise OR sp.id_branch_office = odr.id_branch_office)
                    AND sp.model = odr.model
                    AND sp.size = odr.size
                    AND sp.color = odr.color

On investigation I found the following: MySQL will not be able to distinguish between a column that is NULL considering that I tried the following, but I still have the same problem:

        LEFT JOIN tbl_order odr
            ON sp.code_unique = odr.code_unique
            AND (sp.id_enterprise = odr.id_enterprise OR sp.id_branch_office = odr.id_branch_office)
            AND ((sp.model IS NULL OR odr.model IS NULL) OR sp.model = odr.model)
            AND ((sp.size IS NULL OR odr.size IS NULL) OR sp.size = odr.size)
            AND ((sp.color IS NULL OR odr.color IS NULL) OR sp.color = odr.color)

Desired result:

code_unique	model	size	color	item_total	total_sales	stock
HOLA	null	X	null	1100	10	1090
HOLA	null	XL	null	500	3	497
HOLA	null	null	RED	10	3	7
HOL	null	null	null	300	0	300

Testing: https://www.db-fiddle.com/f/oiz9PeZ9SPM5jWVkmK4aR2/4