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
Did this part of my post not make it to your reader? Your output is almost what I want, except that the A.5 line is matching 1 and 5 in the M table, and I only want it to match the 5. Now, can you suggest how I can get the query to return A,5,5 but not A,5,1? Thanks,David From: Luuk <luu...@gmail.com> To: sqlite-users@mailinglists.sqlite.org Sent: Monday, September 19, 2016 2:43 PM Subject: Re: [sqlite] Complicated join On 19-09-16 19:33, David Bicking wrote: > 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 > ) > ) expected output is missing.... now we need to guess at what you want the output to look like.... > But it doubles up on A,5, matching both on A,1 and A,5 in M which line is correct? (or are they both correct?) > And it doesn't return B,1 with no match even though it is a left join. In my output i do see 'B|1|' ...... sqlite> 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 ...> ) ...> ); A|1|1 A|2|1 A|3|1 A|4|1 A|5|1 A|5|5 A|6|1 A|7|1 A|8|1 A|9|1 B|1| sqlite> .version SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7 sqlite> _______________________________________________ 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