How can I make a mysql that will display all a specific product with different serial number and quantity available. Take note that my client does not have a table that store quantities, what we have are:
- received_purchase_order_detail
- received_transfer_order_detail
- transfer_order_detail
- official_receipt_detail
- ... and many more tables... these are the table we used to know how much more quantity are available.
quantity available = ( received_purchase_order_detail+received_transfer_order_detail )-( transfer_order_detail+official_receipt_detail .... )
I already have a query given by FuzzyTree but it shows a group by product quantity.
MySQL Group By Product Id BUT NOT Product that has a Serial Number
What I need now is to query the products id which has different serial numbers and show each quantity available.
Rather than
Product_ID Product_Name Quantity_Available
6 Apple 100
7 Blender 2
8 Toaster 3
I need
Product_ID Product_Name Serial_No Quantity_Available
6 Apple NULL 100
7 Blender b1 0
7 Blender b2 1
7 Blender b3 1
8 Toaster t1 1
8 Toaster t2 1
8 Toaster t3 1
-------
My test sql now Problem: i've been combining a derived table with union and made the s_product_serial group by so that they will separate. But my query is showing all of the product with serial quantity_available to 0, its like they are following the first data of the first product with a serial that is sold.., only the first blender with b1 serial is sold but the other blender should still be available..
Note I added a union query because the products table does not have serial_no column..., the serial are scattered on different tables. So what i did first is union the tables to collect the complete serial_no available. Or maybe I'm approaching this the wrong way?
Problem
Product_ID Product_Name Serial_No Quantity_Available
6 Apple NULL 100
7 Blender b1 0
7 Blender b2 0
7 Blender b3 0
8 Toaster t1 0
8 Toaster t2 0
8 Toaster t3 0
SELECT
tabletest1.i_p_id,
tabletest1.s_product_serial,
(
COALESCE(tabletest1.Product_Total,0) +
COALESCE(table1.Product_Total,0) +
COALESCE(table2.Product_Total,0) +
COALESCE(table3.Product_Total,0)
) AS 'Total QTY Added',
(
COALESCE(tabletest1.Product_Total,0) +
COALESCE(table4.Product_Total,0) +
COALESCE(table5.Product_Total,0) +
COALESCE(table6.Product_Total,0)
) AS 'Total QTY Released',
(
COALESCE(tabletest1.Product_Total,0) +
COALESCE(table1.Product_Total,0) +
COALESCE(table2.Product_Total,0) +
COALESCE(table3.Product_Total,0)
) -
(
COALESCE(tabletest1.Product_Total,0) +
COALESCE(table4.Product_Total,0) +
COALESCE(table5.Product_Total,0) +
COALESCE(table6.Product_Total,0)
) AS 'Current Quantity Available'
FROM
(
..union query..
)
AS tabletest1
LEFT JOIN
(
derivedtable1..
)
AS table1 ON table1.i_p_id = tabletest1.i_p_id
LEFT JOIN
(
derivedtable2..
)
AS table2 ON table2.i_p_id = tabletest1.i_p_id
LEFT JOIN
(
derivedtable3..
)
AS table3 ON table3.i_p_id = tabletest1.i_p_id
LEFT JOIN
(
derivedtable4..
)
AS table4 ON table4.i_p_id = tabletest1.i_p_id
LEFT JOIN
(
derivedtable5..
)
AS table5 ON table5.i_p_id = tabletest1.i_p_id
LEFT JOIN
(
derivedtable6..
)
AS table6 ON table6.i_p_id = tabletest1.i_p_id
GROUP BY
i_p_id,
s_product_serial
