CODEWITHSUNDEEP
sqlpostgresql
Jazzy
Jazzy

Reputation: 77

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'

Upvotes: 0

Views: 238

Answers (3)

whisk
whisk

Reputation: 655

This should work, you can replace the select with what you want if need be. 

SELECT *

FROM    
    Inventory I
    LEFT JOIN InventoryTransaction IT 
        ON I.id = IT.id
    LEFT JOIN InventoryState InS
        ON I.id = InS.id
WHERE
    IS.description = 'SOLD'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269463

I would start with distinct on:

select i.*
from (select distinct on (item_id) it.*, ins.description
      from inventorytransaction it join
           inventorystate ins
           on it.inventory_state = ins.id
      order by item_id, date desc
     ) i
where description = 'SOLD';

Your data structure is rather unclear about the relationship between inventorytransaction and inventory. I don't see an inventory_id of any sort, although I could speculate that it is item_id.

Upvotes: 0

sagi
sagi

Reputation: 40471

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')

Upvotes: 0

Related Questions