MYSQL - Quantity Left - Product with Serial - Multiple Table

Question

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

This question appears to be off-topic because Stackoverflow is not a Code Creation community. Describe what you have tried and where you are going wrong — Daryl Gill
@DarylGill, sorry i forgot to paste my test sql. Its also pretty long. kindly check the edited post — anonymousmetoyou
Please edit the question and show the sample data that would generate the specified results. — Gordon Linoff

anonymousmetoyou anonymousmetoyou · Accepted Answer · 2014-07-27T02:19:59

i think i got it guys.

rather than AS table(n) ON table(n).i_p_id = tabletest(n).i_p_id

it should refer to the serial AS table(n) ON table(n).s_product_serial = tabletest(n).s_product_serial

Now its working fine but base on how long my sql is, im wondering if this is efficient?

MYSQL - Quantity Left - Product with Serial - Multiple Table

-------

1 Answers