EvtNbr cannot be null, and can be 0. (Though I understand if there is any Evtnbr > 0, there can't be an EvtNbr 0, the lowest can be one or higher.)
I need to come up with some test data, if only to test the "I'm pretty sure it doesn't work" solution I came up with. One good bit of news is my boss came back saying I can skip the closest date bit, either the dates match or they don't, but I don't believe that decision will stick, so I am still trying to come up with an answer. David PS. I switched this to send out "plain text", but in the past I have been told reader end up seeing an unable to read small font on their end. ________________________________ From: R Smith <rsm...@rsweb.co.za> To: sqlite-users@mailinglists.sqlite.org Sent: Saturday, September 17, 2016 7:25 AM Subject: Re: [sqlite] Complicated join On 2016/09/15 5:53 PM, David Bicking wrote: > I have two tables: > CREATE TABLE M ( CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY > KEY(CombinedKeyFields, EvtNbr, TransDate));CREATE TABLE E ( > CombineKeyFields, EvtNbr, TransDate, OtherFields, PRIMARY > KEY(CombinedKeyFields, EvtNbr, TransDate)); > "CombinedKeyFields" is shorthand for a combination of about a half dozen > fields in the primary key."TransDate" is an integer storing a proprietary > date sequence number, where an older date is always less than a newer date > Now, I want to do E LEFT JOIN M > (1) The CombinedKeyFields must always match in each table(2) Match using the > EvtNbr, but if no match, use the lowest M.EvtNbr that matches the > CombinedKeyFields > > (3) Match using the TransDate but if no exact match, match on the M.TransDate > that is less than the E.TransDate but greater than the prior E.TransDate > For M.TransDate = 94E.TransDate = 96 will match to 94but E.TransDate = 98 > will have no match because 94 is less than the prior trans at 96..The idea is > to find the closest date that matches that couldn't be matched to another > record. Hi David, I was going to do this for fun, but some anomalies exist in your description which needs to be understood first. 1 - Does every entry have an EvtNbr? Or can it be null? What is the lowest EvtNbr in every set - 1 or 0 or something else or indeterminate? If an EvtNbr cannot be Null, then point (3) above is moot because there will be no item with a date that matches CombinedKeyFields that doesn't also have a lowest EvtNbr as can be matched by requirement (2). If the lowest EvtNbr for any entry is 0 or 1 then you can simply look for that EvtNbr if a match is not found for the exact EvtNbr - which would always exist unless there are NO matches on CombinedKeyFields in which case there should be no matching lines at all as given by (1). Perhaps if you could (as suggested by another poster) send a full schema with some data and an example of the output needed (highlighting all the possible check cases as described above). If clarity on these can be had, the SQL is quite possible and not too convoluted even (though the jury is out on efficiency as yet). > Saying a prayer that the email gods won't wrap all these lines together in to > an unreadable mess like the last time I asked for advice here... Apparently prayer is not an effective technology :) The e-mail Gods are not at fault here, perhaps the "Send as" settings in your mail composition client can be explored? Many formats should accommodate correct formatting. I'm guessing your client has a plain-text editor that wraps the result into an HTML paragraph (or some other weirdness that you can hopefully deduce and fix by checking the settings). _______________________________________________ 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
