CODEWITHSUNDEEP
mysqlsqlinventory
Primoz Rome
Primoz Rome

Reputation: 11021

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

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

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.

Upvotes: 0

Views: 427

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you just need a JOIN:

SELECT st.product_id, st.lot_id, st.warehouse_location_id, 
       st.quantity as qty
       GROUP_CONCAT(sts.serial_id) as serials
FROM stock_transactions st JOIN
     stock_transactions_serial sts
     ON sts.stock_transaction_id = st.id
GROUP BY st.product_id, st.lot_id, st.warehouse_location_id, st.quantity
HAVING qty > 0;

Note: This will produce a comma-delimited list of the serial numbers.

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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;

Upvotes: 1

Related Questions