Something that works, but is ugly so I hesitate to post it. Again, working with the results of a simpler query outside of SQL would be preferred. Just because you <can> make ASCII-art of the Mandelbrot set using SQL doesn't mean it's the best choice. (Would that now be UTF-8-art? Doesn't have quite the same ring to it)
Playing around does leave me with one question by the way: Do temp tables created via "with a as b" not have a rowid field? I tried referencing that, but kept getting issues. But in any case: create table E (CombinedKeyField text, EvtNbr int, primary key (CombinedKeyField, EvtNbr)); create table M (CombinedKeyField text, EvtNbr int, primary key (CombinedKeyField, EvtNbr)); insert into E values ('A', 1), ('A', 2), ('A', 3), ('A', 4), ('A', 5), ('A', 6), ('A', 7), ('A', 8), ('A', 9), ('B', 1); insert into M values ('A', 1), ('A', 5); --explain query plan with x as ( select CombinedKeyField, E.EvtNbr as EEvtNbr, M.EvtNbr as MEvtNbr, E.EvtNbr != M.EvtNbr as neq from E inner join M using (CombinedKeyField)) --order by CombinedKeyField, EEvtNbr, neq, MEvtNbr) --Use x instead of M for the outer join, and take only the first record (if any) that matches. --Had the "order by" in there while trying to use the rowid in the later compare, --just realized I could take it out since I gave up on using rowid. select E.CombinedKeyField, E.EvtNbr as EEvtNbr, x.MEvtNbr from E left outer join x on E.CombinedKeyField = x.CombinedKeyField and E.EvtNbr = x.EEvtNbr --The "take only the first one" part. where not exists ( select * from x as xt where xt.CombinedKeyField = E.CombinedKeyField and xt.EEvtNbr = E.EvtNbr and (xt.neq < x.neq or (xt.neq = x.neq and xt.MEvtNbr < x.MEvtNbr))); Output is: CombinedKeyField|EEvtNbr|MEvtNbr A|1|1 A|2|1 A|3|1 A|4|1 A|5|5 A|6|1 A|7|1 A|8|1 A|9|1 B|1| explain query plan output: selectid|order|from|detail 1|0|0|SCAN TABLE E 1|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 (CombinedKeyField=?) 0|0|0|SCAN TABLE E 0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (EEvtNbr=? AND CombinedKeyField=?) 0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2 2|0|0|SEARCH TABLE E USING COVERING INDEX sqlite_autoindex_E_1 (CombinedKeyField=? AND EvtNbr=?) 2|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1 (CombinedKeyField=?) The compares and such are going to blow up in size when translated to your real version with the 8 fields, which is what makes me cringe. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Bicking Sent: Monday, September 19, 2016 1:34 PM To: SQLite mailing list Subject: Re: [sqlite] Complicated join 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users