Complicated join

classic Classic list List threaded Threaded
15 messages Options
Reply | Threaded
Open this post in threaded view
|

Complicated join

David Bicking-2
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Raymond
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 with long "where not exists..." clauses etc, and I've probably missed something. If you could provide a sample set of insert statements to paste in along with "here's what I hope to see at the end from this" that would help out.

Also, when you ask "Can this be done in SQL?" are you asking...
-in a single statement?
-in only SQL, but multiple statements are ok (such as using intermediate temp tables)?
-with an initial SQL query, but then the ability to muck about with the returned results in the language of your choice thereafter?
-something else?

Thanks,

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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(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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

Simon Slavin-3

On 15 Sep 2016, at 9:46pm, David Raymond <[hidden email]> 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 closest date that matches that couldn't be matched to another record" by itself requires processing every row of a table using a metric you haven't defined.

If I did do it I'd use multiple parses.  One parse to work out the matching key values for each table and store them in another column of the table, the final parse to do the LEFT JOIN query.

But your question is phrased not in terms of set operations SQL implements but in terms of a standard procedural programming language, so perhaps you should use one.  Sooner or later you're going to have to do some programming.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

Deon Brewis
In reply to this post by David Raymond
OVER PARTITION BY ...


One can dream...

- Deon

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of David Raymond
Sent: Thursday, September 15, 2016 1:47 PM
To: SQLite mailing list <[hidden email]>
Subject: 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 with long "where not exists..." clauses etc, and I've probably missed something. If you could provide a sample set of insert statements to paste in along with "here's what I hope to see at the end from this" that would help out.

Also, when you ask "Can this be done in SQL?" are you asking...
-in a single statement?
-in only SQL, but multiple statements are ok (such as using intermediate temp tables)?
-with an initial SQL query, but then the ability to muck about with the returned results in the language of your choice thereafter?
-something else?

Thanks,

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] 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(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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

R Smith
In reply to this post by David Bicking-2

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, 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.

Hi David,

I was going to do this for fun, but some anomalies exist in your
description which needs to be understood first.

1 - Does every entry have an EvtNbr? Or can it be null? What is the
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?  
If an EvtNbr cannot be Null, then point (3) above is moot because there
will be no item with a date that matches CombinedKeyFields that doesn't
also have a lowest EvtNbr as can be matched by requirement (2). If the
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that
EvtNbr if a match is not found for the exact EvtNbr - which would always
exist unless there are NO matches on CombinedKeyFields in which case
there should be no matching lines at all as given by (1).

Perhaps if you could (as suggested by another poster) send a full schema
with some data and an example of the output needed (highlighting all the
possible check cases as described above).

If clarity on these can be had, the SQL is quite possible and not too
convoluted even (though the jury is out on efficiency as yet).


> 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...

Apparently prayer is not an effective technology :)  The e-mail Gods are
not at fault here, perhaps the "Send as" settings in your mail
composition client can be explored?  Many formats should accommodate
correct formatting. I'm guessing your client has a plain-text editor
that wraps the result into an HTML paragraph (or some other weirdness
that you can hopefully deduce and fix by checking the settings).

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Bicking-2
EvtNbr cannot be null, and can be 0. (Though I understand if there is any Evtnbr > 0, there can't be an EvtNbr 0, the lowest can be one or higher.)


I need to come up with some test data, if only to test the "I'm pretty sure it doesn't work" solution I came up with.

One good bit of news is my boss came back saying I can skip the closest date bit, either the dates match or they don't, but I don't believe that decision will stick, so I am still trying to come up with an answer.
David


PS. I switched this to send out "plain text", but in the past I have been told reader end up seeing an unable to read small font on their end.

________________________________
From: R Smith <[hidden email]>
To: [hidden email]
Sent: Saturday, September 17, 2016 7:25 AM
Subject: Re: [sqlite] Complicated join



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, 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.

Hi David,

I was going to do this for fun, but some anomalies exist in your
description which needs to be understood first.

1 - Does every entry have an EvtNbr? Or can it be null? What is the
lowest EvtNbr in every set - 1 or 0 or something else or indeterminate?  
If an EvtNbr cannot be Null, then point (3) above is moot because there
will be no item with a date that matches CombinedKeyFields that doesn't
also have a lowest EvtNbr as can be matched by requirement (2). If the
lowest EvtNbr for any entry is 0 or 1 then you can simply look for that
EvtNbr if a match is not found for the exact EvtNbr - which would always
exist unless there are NO matches on CombinedKeyFields in which case
there should be no matching lines at all as given by (1).

Perhaps if you could (as suggested by another poster) send a full schema
with some data and an example of the output needed (highlighting all the
possible check cases as described above).

If clarity on these can be had, the SQL is quite possible and not too
convoluted even (though the jury is out on efficiency as yet).



> 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...

Apparently prayer is not an effective technology :)  The e-mail Gods are
not at fault here, perhaps the "Send as" settings in your mail
composition client can be explored?  Many formats should accommodate
correct formatting. I'm guessing your client has a plain-text editor
that wraps the result into an HTML paragraph (or some other weirdness
that you can hopefully deduce and fix by checking the settings).

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

James K. Lowden
In reply to this post by David Bicking-2
On Thu, 15 Sep 2016 15:53:10 +0000 (UTC)
David Bicking <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Bicking-2
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 <[hidden email]>
To: [hidden email]
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

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 field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

Luuk
In reply to this post by David Bicking-2
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
>                 )
> )

expected output is missing....

now we need to guess at what you want the output to look like....

> But it doubles up on A,5, matching both on A,1 and A,5 in M
which line is correct? (or are they both correct?)
> And it doesn't return B,1 with no match even though it is a left join.
In my output i do see 'B|1|' ......


sqlite> 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
    ...>                )
    ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Bicking-2
In reply to this post by Luuk
"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 <[hidden email]>
 To: [hidden email]
 Sent: Monday, September 19, 2016 2:23 PM
 Subject: 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)
> , ('A', 6)
> , ('A', 7)
> , ('A', 8)
> , ('A', 9)
> , ('B', 1);
>
>

What is the name of this field?

CombinedKeyField
CombineKeyField
CombinedKeyFields


And why is it not possible to keep this the SAME/UNCHANGED between posts?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Bicking-2
In reply to this post by Luuk
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 part of my post not make it to your reader?
Your output is almost what I want, except that the A.5 line is matching 1 and 5 in the M table, and I only want it to match the 5.

Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
Thanks,David
     From: Luuk <[hidden email]> To: [hidden email]
 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.EvtNbr = (SELECT MIN(M1.EvtNbr) FROM M M1
>                            WHERE M1.CombinedKeyField = E.CombinedKeyField
>                )
> )

expected output is missing....

now we need to guess at what you want the output to look like....

> But it doubles up on A,5, matching both on A,1 and A,5 in M
which line is correct? (or are they both correct?)
> And it doesn't return B,1 with no match even though it is a left join.
In my output i do see 'B|1|' ......


sqlite> 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
    ...>                )
    ...> );
A|1|1
A|2|1
A|3|1
A|4|1
A|5|1
A|5|5
A|6|1
A|7|1
A|8|1
A|9|1
B|1|
sqlite> .version
SQLite 3.11.1 2016-03-03 16:17:53 f047920ce16971e573bc6ec9a48b118c9de2b3a7
sqlite>



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Raymond
In reply to this post by David Bicking-2
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:[hidden email]] 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 <[hidden email]>
To: [hidden email]
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

David Bicking-2
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 <[hidden email]>
 To: SQLite mailing list <[hidden email]>
 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:[hidden email]] 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 <[hidden email]>
To: [hidden email]
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


   
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Complicated join

Luuk
In reply to this post by David Bicking-2


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 part of my post not make it to your reader?
> Your output is almost what I want, except that the A.5 line is matching 1 and 5 in the M table, and I only want it to match the 5.
>
> Now, can you suggest how I can get the query to return A,5,5 but not A,5,1?
> Thanks,David

sqlite> select E.CombinedKeyField, E.EvtNbr, max(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
    ...>                )
    ...> ) GROUP BY E.CombinedKeyField, E.EvtNbr ;
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|
sqlite>


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users