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?
Thanks,David
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 =
E.CombinedKeyField
...> )
...> ) GROUP BY E.CombinedKeyField, E.EvtNbr ;
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|
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
