I need my query to pick up OIBT records matching a condition and at the same time pick up the last IBT1 (with base type = 16, i.e. Returns). Most of the solutions for limiting the join uses a subquery based on a key. The OIBT and IBT1 tables do not seem to have one set of keys. This is my query:
select a.ItemCode, a.BatchNum, a.WhsCode, a.BaseType, a.BaseNum, a.CreateDate, a.Quantity, b.BaseType, b.BaseNum, b.DocDate from OIBT a left join IBT1 b on a.ItemCode = b.ItemCode and a.BatchNum = b.BatchNum and a.WhsCode = b.WhsCode and F.BaseType =16 where and a.Quantity >0
The query joins multiple IBT1 records. How do I structure my query so that the last, i.e. latest IBT1 record is selected?