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
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
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
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
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)
&
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
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
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
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
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
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,
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
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
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
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
15 matches
Mail list logo