Thanks.
Yeah, sometimes I hate SQL. Some of my queries for this project take 2 or 3
pages of paper to print out. Unfortunately the only alternatives approved by
the bosses are even worse.
I think I can add the Not exists clause to my query and that should do it.
Which means I need to load 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 <[email protected]>
To: SQLite mailing list <[email protected]>
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, working with
the results of a simpler query outside of SQL would be preferred. Just because
you <can> make ASCII-art of the Mandelbrot set using SQL doesn't mean it's the
best choice. (Would that now be UTF-8-art? Doesn't have quite the same ring to
it)
Playing around does leave me with one question by the way: Do temp tables
created via "with a as b" not have a rowid field? I tried referencing that, but
kept getting issues.
But in any case:
create table E (CombinedKeyField text, EvtNbr int, primary key
(CombinedKeyField, EvtNbr));
create table M (CombinedKeyField text, EvtNbr int, primary key
(CombinedKeyField, EvtNbr));
insert into E values ('A', 1), ('A', 2), ('A', 3), ('A', 4), ('A', 5), ('A',
6), ('A', 7),
('A', 8), ('A', 9), ('B', 1);
insert into M values ('A', 1), ('A', 5);
--explain query plan
with x as (
select CombinedKeyField, E.EvtNbr as EEvtNbr, M.EvtNbr as MEvtNbr, E.EvtNbr !=
M.EvtNbr as neq
from E inner join M using (CombinedKeyField))
--order by CombinedKeyField, EEvtNbr, neq, MEvtNbr)
--Use x instead of M for the outer join, and take only the first record (if
any) that matches.
--Had the "order by" in there while trying to use the rowid in the later
compare,
--just realized I could take it out since I gave up on using rowid.
select E.CombinedKeyField, E.EvtNbr as EEvtNbr, x.MEvtNbr
from E left outer join x
on E.CombinedKeyField = x.CombinedKeyField and E.EvtNbr = x.EEvtNbr
--The "take only the first one" part.
where not exists (
select * from x as xt
where xt.CombinedKeyField = E.CombinedKeyField and xt.EEvtNbr = E.EvtNbr
and (xt.neq < x.neq or (xt.neq = x.neq and xt.MEvtNbr < x.MEvtNbr)));
Output is:
CombinedKeyField|EEvtNbr|MEvtNbr
A|1|1
A|2|1
A|3|1
A|4|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
explain query plan output:
selectid|order|from|detail
1|0|0|SCAN TABLE E
1|1|1|SEARCH TABLE M USING COVERING INDEX sqlite_autoindex_M_1
(CombinedKeyField=?)
0|0|0|SCAN TABLE E
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (EEvtNbr=? AND
CombinedKeyField=?)
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 2
2|0|0|SEARCH TABLE E USING COVERING INDEX sqlite_autoindex_E_1
(CombinedKeyField=? AND 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:[email protected]] On
Behalf Of David Bicking
Sent: Monday, September 19, 2016 1:34 PM
To: SQLite mailing list
Subject: Re: [sqlite] Complicated join
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);
I'm ignoring the TransDate part for now.
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
This is the closest I have got
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
)
)
But it doubles up on A,5, matching both on A,1 and A,5 in M
And it doesn't return B,1 with no match even though it is a left join.
Hopefully that comes out readable, and my needs are clearer.
Thanks,
David
----- Original Message -----
From: James K. Lowden <[email protected]>
To: [email protected]
Sent: Monday, September 19, 2016 10:57 AM
Subject: Re: [sqlite] Complicated join
On Thu, 15 Sep 2016 15:53:10 +0000 (UTC)
David Bicking <[email protected]> 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 the
> M.TransDate that is less than the E.TransDate but greater than the
> prior E.TransDate
I think this is what you describe:
select E.CombinedKeyFields, max(M.EvtNbr) as EvtNbr, max(M.TransDate)
as TransDate from E left join M
on E.CombinedKeyFields = M.CombinedKeyFields
and E.EvtNbr >= M.EvtNbr
and E.TransDate >= M.TransDate
--jkl
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
