Re: [sqlite] Complicated join

2016-09-20 Thread Luuk
On 19-09-16 21:28, David Bicking wrote: 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

Re: [sqlite] Complicated join

2016-09-19 Thread David Bicking
the 70,000+ records to M and the 200,000+ records to E and see how long it takes to run! David From: David Raymond <david.raym...@tomtom.com> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Sent: Monday, September 19, 2016 3:30 PM Subject: Re: [sqlite] Comp

Re: [sqlite] Complicated join

2016-09-19 Thread David Raymond
w up in size when translated to your real version with the 8 fields, which is what makes me cringe. -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Bicking Sent: Monday, September 19, 2016 1:34 PM To: SQLite mailing list Su

Re: [sqlite] Complicated join

2016-09-19 Thread David Bicking
te.org 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.Ev

Re: [sqlite] Complicated join

2016-09-19 Thread David Bicking
ect: Re: [sqlite] Complicated join On 19-09-16 19:33, David Bicking wrote: > INSERT INTO M (CombinedKeyField, EvtNbr) > > VALUES ('A', 1), > ('A', 5); > > INSERT INTO E (CombineKeyField, EvtNbr) > VALUES ('A', 1) > , ('A', 2) > , ('A', 3) > , ('A', 4) > , ('A', 5) &

Re: [sqlite] Complicated join

2016-09-19 Thread Luuk
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

Re: [sqlite] Complicated join

2016-09-19 Thread Luuk
On 19-09-16 19:33, David Bicking wrote: INSERT INTO M (CombinedKeyField, EvtNbr) VALUES ('A', 1), ('A', 5); INSERT INTO E (CombineKeyField, EvtNbr) VALUES ('A', 1) , ('A', 2) , ('A', 3) , ('A', 4) , ('A', 5) , ('A', 6) , ('A', 7) , ('A', 8) , ('A', 9) , ('B', 1); What is the name of this

Re: [sqlite] Complicated join

2016-09-19 Thread David Bicking
ber 19, 2016 10:57 AM Subject: Re: [sqlite] Complicated join On Thu, 15 Sep 2016 15:53:10 + (UTC) David Bicking <dbic...@yahoo.com> wrote: > (1) The CombinedKeyFields must always match in each table(2) Match > using the EvtNbr, but if no match, use the lowest M.Evt

Re: [sqlite] Complicated join

2016-09-19 Thread James K. Lowden
On Thu, 15 Sep 2016 15:53:10 + (UTC) David Bicking wrote: > (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

Re: [sqlite] Complicated join

2016-09-17 Thread David Bicking
d 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 Bicki

Re: [sqlite] Complicated join

2016-09-17 Thread R Smith
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,

Re: [sqlite] Complicated join

2016-09-16 Thread Deon Brewis
ct: 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 wit

Re: [sqlite] Complicated join

2016-09-15 Thread Simon Slavin
On 15 Sep 2016, at 9:46pm, David Raymond wrote: > The idea is to find the closest date that matches that couldn't be matched to > another record. [snip] > Can this join be done in SQL? I wouldn't even try to do it in any SQL engine. It would be ridiculously

Re: [sqlite] Complicated join

2016-09-15 Thread David Raymond
ite-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(Combin

[sqlite] Complicated join

2016-09-15 Thread David Bicking
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