MySql MINUS (opposite of intersaction)

Question

I have two tables

• inventory_lot_transactions and
• inventory_lot_serials

I am logging all the transactions IN and OUT of inventory by lot's. Every lot that gets booked in/out from warehouse has it's own batch of serial numbers that get logged for every IN and OUT transaction.

Now I would like to get all the serial numbers that are still available in X warehouse. Basically I would like to achieve this:

SELECT serials FROM table_a where transaction_type=In
MINUS
SELECT serials FROM table_a where transaction_type=Out

I have prepared two SQL fiddles:

1. This is the list A of serials that came into one warehouse http://sqlfiddle.com/#!9/3a4ab/7
2. This is the list B of serials that went out of the warehouse http://sqlfiddle.com/#!9/3a4ab/8

Basically I would like to select all the serials that are still available in this warehouse. E.g. List A - List B.

Answer 1

Not sure if I understood correctly, you should be able to use NOT IN in your where-condition. http://sqlfiddle.com/#!9/3a4ab/10

select IL.lot_id, ILS.serial_id 
from inventory_lot_serials ILS
left join inventory_lots IL ON IL.id=ILS.inventory_lot_id
where 
  IL.type='In' and 
  IL.warehouse_location_id=500
  AND ILS.serial_id NOT IN 
(SELECT ILS.serial_id 
    from inventory_lot_serials ILS
    left join inventory_lots IL ON IL.id=ILS.inventory_lot_id
    where 
      IL.type='Out' and 
  IL.warehouse_location_id=500)