gistfile1.txt

Stock Item (SI) - an item
Movement History (H) - a movement of one or more stock items from A to B. Has 1 or more Reception information
Reception Information (RI) - Details on the reception conditions. One RI per SI (eg if H is a movement of 2 stock item, it will have 2 RI)

Query - I want to find missmatches between current location of the SI and what the most recent movement history for that item says that stock item went to. Something similar to:

SELECT h.*,ri.code_id,si.*
  FROM iwrs.movement_history h inner join iwrs.reception_information ri on ri.movement_history_id = h.id
inner join iwrs.stock_item si on si.id=ri.code_id
where ri.code_id in
(
SELECT max(h.reception_date),ri.code_id
  FROM iwrs.movement_history h inner join iwrs.reception_information ri on ri.movement_history_id = h.id
inner join iwrs.stock_item si on si.id=ri.code_id
group by ri.code_id
)