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 <[email protected]> To: [email protected]
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
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
