INSERT INTO M (CombinedKeyField, EvtNbr) VALUES ('A', 1), ('A', 5);
INSERT INTO E (CombineKeyField, EvtNbr) VALUES ('A', 1) , ('A', 2) , ('A', 3) , ('A', 4) , ('A', 5) , ('A', 6) , ('A', 7) , ('A', 8) , ('A', 9) , ('B', 1); I'm ignoring the TransDate part for now. This is what I want: SELECT E.CombinedKeyField, E.EvtNbr, M.EvtNbr; A 1 1 A 2 1 A 3 1 A 4 1 A 5 5 -- matches the (A,5) record in the M table. A 6 1 A 7 1 A 8 1 A 9 1 B 1 NULL -- no match found for CombinedKeyfield in M This is the closest I have got select E.CombinedKeyField, E.EvtNbr, M.EvtNbr from E left join M on E.CombinedKeyField = M.CombinedKeyField and (E.EvtNbr = M.EvtNbr or M.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1 WHERE M1.CombinedKeyField = E.CombinedKeyField ) ) But it doubles up on A,5, matching both on A,1 and A,5 in M And it doesn't return B,1 with no match even though it is a left join. Hopefully that comes out readable, and my needs are clearer. Thanks, David ----- Original Message ----- From: James K. Lowden <jklow...@schemamania.org> To: sqlite-users@mailinglists.sqlite.org Sent: Monday, September 19, 2016 10:57 AM Subject: Re: [sqlite] Complicated join On Thu, 15 Sep 2016 15:53:10 +0000 (UTC) David Bicking <dbic...@yahoo.com> wrote: > (1) The CombinedKeyFields must always match in each table(2) Match > using the EvtNbr, but if no match, use the lowest M.EvtNbr that > matches the CombinedKeyFields > > (3) Match using the TransDate but if no exact match, match on the > M.TransDate that is less than the E.TransDate but greater than the > prior E.TransDate I think this is what you describe: select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate) as TransDate from E left join M on E.CombinedKeyFields = M.CombinedKeyFields and E.EvtNbr >= M.EvtNbr and E.TransDate >= M.TransDate --jkl _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users