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
