CODEWITHSUNDEEP
sqlpostgresql
TillTheDayIDie
TillTheDayIDie

Reputation: 67

Is there an elegant way to write specific query in SQL?

enter image description hereI have three tables: INVENTORY, TRANSFER, INVENTORY_TRANSFER.

Transfers can have three states: ACCEPTED, PENDING, DECLINED.

If transfer has status 'ACCEPTED' or 'DECLINED' the inventory can be transferred again. It means we can create a new row in TRANSFER. And create a raw in INVENTORY_TRANSFER and assign in it inventory id and transfer id

So I need to get all inventory, which wasn't transferred or the last transfer(inventory_transfer with the largest id) with this inventory has status 'ACCEPTED' or 'DECLINED'.

If inventory wasn't transferred it means there are not records in inventory_transfer with specific inventory and transfer

So, i understand that i need to do

SELECT * FROM INVENTORY i 
LEFT JOIN INVENTORY_TRANSFER it on i.inventory_id = it.invetory_id
LEFT JOIN TRANSFER t on it.transfer_id = t.transfer_id
WHERE ???

Is there elegant way to get inventory where the last transfer with inventory has not 'PENDING' status.

Upvotes: 0

Views: 75

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270181

If I understand your question correctly, you can find the last transfer record using distinct on and then find whether that matches your condition:

select i.*
from inventory i left join
     (select distinct on (it.inventory_id) it.inventory_id, t.status
      from inventory_transfer it join
           transfer t
           on it.transfer_id = t.transfer_id
      order by it.inventory_id, it.created_datetime desc
     ) it
     on it.inventory_id = i.inventory_id
where it.inventory_id is null or  -- no transfers 
      it.status in ('ACCEPTED', 'DECLINED');

This uses the created timestamp to determine the "last" record. Your question is unclear on whether this should be the created or updated timestamp.

Upvotes: 1

Related Questions