OVER PARTITION BY ...
One can dream... - Deon -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Raymond Sent: Thursday, September 15, 2016 1:47 PM To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] Complicated join Can it be done in SQL? Yes. In any sort of pretty or efficient manner? Ehhh, maybe? I came up with something that seems to work with the small sample cases that I came up with to try and cover your requirements there, but it's got a couple levels of CTE's with long "where not exists..." clauses etc, and I've probably missed something. If you could provide a sample set of insert statements to paste in along with "here's what I hope to see at the end from this" that would help out. Also, when you ask "Can this be done in SQL?" are you asking... -in a single statement? -in only SQL, but multiple statements are ok (such as using intermediate temp tables)? -with an initial SQL query, but then the ability to muck about with the returned results in the language of your choice thereafter? -something else? Thanks, -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Bicking Sent: Thursday, September 15, 2016 11:53 AM To: SQLite Mailing List Subject: [sqlite] Complicated join 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. All this data is coming from upstream data, so this is the data we have on hand, though the schema for this reporting package can still be changed, but I would have to justify the change by saying the report can only be done with the change... Can this join be done in SQL? If this were an inner join, I believe I could use CASE statements in the WHERE clause, but I'm not sure that would work moving it to the JOIN... ON clause. Any advice or help is much appreciated. Thanks,David 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... _______________________________________________ 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
