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 | HELLO | null | null | X | null | 10 |
two | null | 1 | HELLO | null | null | XL | null | 3 |
3 | null | 1 | HELLO | null | null | null | NET | 3 |
4 | 1 | null | HELLO | null | null | null | NET | 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 | HELLO | null | null | X | null | 100 |
two | null | 1 | HELLO | null | null | X | null | 1000 |
3 | null | 1 | HELLO | null | null | XL | null | 500 |
4 | null | 1 | HELLO | null | null | null | NET | 10 |
4 | null | 1 | hello | MDLX1 | null | null | null | 300 |
It should be noted that the columns model, size, color
can be with data or empty depending on the type of product. What is required is the unique code of the product ( code_unique
) and to whom that product belongs, here it should be only one 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
that AS
they are the ones in the first SELECT
of the query:
SELECT t.enterprise_establishment,
t.office_establishment,
t.enterprise_tradename,
t.office_tradename,
t.code_unique,
t.model,
t.size,
t.color,
SUM(t.item_total) AS item_total,
t.quantity AS total_sales,
SUM(t.item_total) - t.quantity AS stock
FROM (
SELECT sp.id_stock_product,
ent.establishment AS enterprise_establishment,
bo.establishment AS office_establishment,
ent.tradename AS enterprise_tradename,
bo.tradename AS office_tradename,
sp.code_unique,
sp.model,
sp.size,
sp.color,
max(sp.item_total) over (PARTITION BY id_stock_product) AS item_total,
SUM(odr.quantity) AS quantity
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 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
WHERE ac.id_user = 2
GROUP BY sp.code_unique, sp.model, sp.size, sp.color, sp.id_stock_product, sp.item_total
) t
GROUP BY t.code_unique, t.model, t.size, t.color, t.quantity
Problems:
t.quantity AS total_sales
:- You are subtracting sales from all the results in the table
tbl_stock_product
, despite having a different size, color or model. - When there are no sales records in the table
tbl_order
, it doesn't show anything, ideally it would show 0.
- You are subtracting sales from all the results in the table
SUM(t.item_total) - t.quantity AS stock
:- When there are no sales records in the table
tbl_order
, it does not show anything, the ideal would be to return to showing the total of available products as well asSUM(t.item_total) AS item_total
axis:300 productos disponibles
-
0 ventas
=
300
.
- When there are no sales records in the table
What is affecting or causing this problem are the following conditions passed to the table tbl_order
:
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
Investigating I found the following: MySQL will not be able to distinguish between a column that is NULL
considering that try 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 |
---|---|---|---|---|---|---|
HELLO | null | X | null | 1100 | 10 | 1090 |
HELLO | null | XL | null | 500 | 3 | 497 |
HELLO | null | null | NET | 10 | 3 | 7 |
hello | null | null | null | 300 | 0 | 300 |
Test in online compiler: https://www.db-fiddle.com/f/q3iEtNW28frgwETSgVV6ZQ/3
Previous question:
How to exclude GROUP BY from a specific LEFT JOIN?
Note: In the previous question it had been commented that the objective was to obtain an inventory and, I realized that I also needed to add other columns to the table
tbl_order
that the column was not enoughcode_unique
since with that table it is not possible to know how many productsx
with size or color or model were sold.
Discussion to reach this result: