Jazzy Jazzy - 1 month ago 6
SQL Question

Select subquery where last row of many-to-many relation is equals to condition

I have 3 tables: Inventory, InventoryTransaction, InventoryState.
What im trying to do is select all items from Inventory where last row of InventoryTransaction is in InventoryState euqlas 'SOLD'


note: 1 Item can have multiple transactions, so i need to get all items that the last transaction item state is SOLD


Tables:

Inventory
---------------------
id | item_name | date
1 | book | 2016


InventoryTransaction
----------------------------------------------
id | amount | item_id | inventory_state | date
1 | 20.00 | 1 | 1 | 2016


InventoryState
-----------------
id | description
1 | 'SOLD'

Answer

Try using a correlated sub query to fetch the latest inventory_state from the transaction table, and then join by it to the state table:

SELECT t.id
FROM(
    SELECT i.*,
           (SELECT it.inventory_state FROM InventoryTransaction it
            WHERE it.item_id = i.id
            ORDER BY it.id DESC
            LIMIT 1) as last_inv_state_id
    FROM inventory i) t
JOIN InventoryState invs
 ON(t.last_inv_state_id = invs.id AND invs.description = 'SOLD')
Comments