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
