MySQL get the difference on joined table - stock transactions/inventory system

Question

I am not sure how to ask this, but I will try anyway. I have a DB structure that tracks stock inventory for each product, product lots and warehouse locations. This allows me to know exactly what products are on stock, at what price and on what location.

Basically I use two tables for tracking

• stock_transactions [id, product_id, lot_id, warehouse_location_id, type, quantity]

  1. product_id = Unique identifier for SKU item in the warehouse
  2. lot_id = Unique identifier for product lots that arrived into the warehouse.
  3. warehouse_location_id = Unique identifier of warehouse location
  4. type = type of transaction [IN = into location, OUT = OUT of location]

• stock_transactions_serials [stock_transaction_id, serial_id]`:

I am creating an entry in stock_transactions for every item we move from warehouse location to location. E.g. if I move 5 quantity of product A from location X to location y I will create two entries:

INSERT INTO stock_transactions 
(id, product_id, lot_id, warehouse_location_id, type, quantity) 
VALUES (1, 'A', 'Aq2er4', 'X', 'Out', -5)

INSERT INTO stock_transactions 
(id, product_id, lot_id, warehouse_location_id, type, quantity) 
VALUES (1, 'A', 'Aq2er4', 'Y', 'In', 5)

Some products we track via serial numbers. For example if above product would have serial numbers we would use barcode reader to scan which serail numbers of product A we have moved to another locations. The table stock_transactions_serials will track serial numbers for each stock transaction.

Now I have two problems that I need to solve:

1. I need to get the list of products and quantities on stock (grouped by product_id, lot_id and location_id. I have achieved this by:

SELECT id, product_id, lot_id, warehouse_location_id, sum(ST.quantity) as qty
FROM stock_transactions ST 
GROUP BY product_id, lot_id, warehouse_location_id
HAVING qty > 0

I have created SQLFiddle with some data here: http://sqlfiddle.com/#!9/fb352/2.

2. I need to get the right serial numbers for that stock. I don't know how to tackle this...

If I join the tables I don't get the right results, or my stock SUM's are wrong. Any ideas? I would need to somehow get the difference between In and Out transactions and get the right serials.

Answer 1

It seems product_id + lot_id + warehouse_location_id is the natural key for stock_transactions. You chose to add a surrogate id, so a record gets uniquely identified both by either its natural key or its ID. You chose to group by the natural key, but you could just as well have grouped by id. Is this assumption correct?

In that case you can simply aggregate the serial numbers per stock_transactions.id (i.e. concatenate them) and join this list:

SELECT
  ST.id, 
  ST.product_id, 
  ST.lot_id, 
  ST.warehouse_location_id, 
  sum(ST.quantity) as qty,
  STS.serials
FROM stock_transactions ST 
LEFT JOIN 
(
  SELECT stock_transaction_id, GROUP_CONCAT(serial_id) AS serials
  FROM stock_transactions_serials
  GROUP by stock_transaction_id
) STS ON STS.stock_transaction_id = ST.id 
GROUP BY ST.product_id, ST.lot_id, ST.warehouse_location_id
HAVING qty > 0;