Skip to Content
Dec 16, 2021 at 04:49 AM

How do I limit the the join of IBT1 to OIBT To one record


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
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?