On 19-09-16 21:28, David Bicking wrote:
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?
sqlite> select E.CombinedKeyField, E.EvtNbr, max(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 =
...> ) GROUP BY E.CombinedKeyField, E.EvtNbr ;
sqlite-users mailing list