CREATE CURSOR table1 (setid I, deptid I, effd T) CREATE CURSOR table2 (setid I, deptid I, effdt T) INSERT INTO table1 VALUES (1, 1, DATE() - 2) INSERT INTO table1 VALUES (1, 2, DATE() - 2) INSERT INTO table2 Values(1, 1, DATE() - 4) INSERT INTO table2 Values(1, 1, DATE() - 3) INSERT INTO table2 Values(1, 1, DATE() - 2) INSERT INTO table2 Values(1, 1, DATE() - 1) INSERT INTO table2 Values(1, 1, DATE() - 0) SELECT Table1.*, ; A.*, ; Table2.* ; FROM Table1 ; LEFT OUTER JOIN ( ; SELECT MAX(B.effdt) as maxeffdt, ; B.SetID, ; B.DeptID ; FROM Table2 B ; WHERE B.effdt < Table1.effd ; GROUP BY B.SetID, B.DeptID ; ) A ON Table1.setid = A.setid AND Table1.deptid = A.deptid ; LEFT OUTER JOIN Table2 ON Table1.setid = Table2.setid AND Table1.deptid = Table2.deptid AND Table2.effdt = A.maxeffdt