This join finds 1 student  see M1Average, M2Average in output.

 left outer join Marks M1 on (M.StudentID = M1.StudentID) and (M.CourseID = M1.CourseID) and (1 = M1.TermID) 
 left outer join Marks M2 on (M.StudentID = M2.StudentID) and (M.CourseID = M2.CourseID) and (2 = M2.TermID) 
 

Reorder the fields in join to match an index and it works.

-- WORKS - All Students have M1Average, M2Average (as they should)
-- I changes the order of  
-- from: on (M.StudentID = M1.StudentID) and (M.CourseID = M1.CourseID)
-- to:   on (M.CourseID = M1.CourseID) and (M.StudentID = M1.StudentID)      

  left outer join Marks M1 on (M.CourseID = M1.CourseID) and (M.StudentID = M1.StudentID) and (1 = M1.TermID)  
  left outer join Marks M2 on (M.CourseID = M2.CourseID) and (M.StudentID = M2.StudentID) and (2 = M2.TermID)  

---

 Comments

---

The issue was with an optimization in DBISAM to prevent re-executing joins whose parent driver table values have not changed. In certain cases this optimization was preventing joins from being executed that should have been executed, thus resulting in fewer records and incorrect results.

---

 Resolution

---

Fixed Problem on 5/28/2020 in version 4.49 build 1

---

 Products Affected

---

DBISAM Additional Software and Utilities
DBISAM ODBC Client-Server
DBISAM ODBC Client-Server with Source
DBISAM ODBC Standard
DBISAM ODBC Standard with Source
DBISAM ODBC Trial
DBISAM VCL Client-Server
DBISAM VCL Client-Server with Source
DBISAM VCL Standard
DBISAM VCL Standard with Source
DBISAM VCL Trial