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 par
70,000+ records to M and the 200,000+ records to
E and see how long it takes to run!
David
From: David Raymond
To: SQLite mailing list
Sent: Monday, September 19, 2016 3:30 PM
Subject: Re: [sqlite] Complicated join
Something that works, but is ugly so I hesitate to post it. Again
EvtNbr=?)
2|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1
(CombinedKeyField=?)
The compares and such are going to blow 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
, 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.EvtNbr = (SELECT MIN(M1.Ev
"CombinedKeyFields", is in fact about 7 or 8 fields in the natural key. If I
mistype 17 letters, I'd hate to see how I mangle the whole thing.
From: Luuk
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 2:23 PM
Subject: Re: [sqlite] Complica
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 fi
essage -----
From: James K. Lowden
To: sqlite-users@mailinglists.sqlite.org
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join
On Thu, 15 Sep 2016 15:53:10 + (UTC)
David Bicking wrote:
> (1) The CombinedKeyFields must always match in each table(2) Match
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 exact match, match on t
e past I have been told
reader end up seeing an unable to read small font on their end.
From: R Smith
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 Bick
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,
Trans
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
Subject: Re: [sqlite] Complicated join
Can it be
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
difficult to debug. Even "the c
ssage-
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,
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
