Missing data on SELECT

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Missing data on SELECT

jose isaias cabrera-3

Greetings.

Apologies for the long email, or long set of data.  I have these two tables in two different database files, but for easy setup, I have place them in the same DB.  Having these data,


CREATE TABLE ProjectsALL
(
  id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (4,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (5,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (6,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (7,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (8,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (9,'id0','A','ccc','2017-10-05 10:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (10,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (11,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (12,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (13,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (14,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (15,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (16,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (17,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (18,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES (19,'id0','A','ccc','2017-10-05 10:30:19');


CREATE TABLE ProjectsMine
(
  id integer primary key, ProjID integer, login, cust, proj, XtraB
);
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (1,'id0','A','aaa','2017-06-02 15:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (2,'id1','B','bbb','2017-06-03 12:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (3,'id2','A','ccc','2017-08-02 11:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (4,'id2','A','ccc','2017-09-02 17:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (5,'id3','A','ccc','2017-10-02 19:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (6,'id1','C','ccc','2017-10-02 18:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (7,'id1','C','ccc','2017-10-03 13:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (8,'id3','A','ccc','2017-10-04 14:30:19');
INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES (9,'id0','A','ccc','2017-10-15 10:30:19');

running this SELECT,

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND
   cl.XtraB != ls.XtraB
);

would give, 9.  Now let's delete record 10 on the ProjectsALL table,

delete from ProjectsAll where id=10;

after deleting record 10 and running the same SELECT above,

sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
   ...> (
   ...>    cl.id = ls.id AND
   ...>    cl.login = 'id0' AND
   ...>    cl.id = ls.id AND
   ...>    cl.XtraB != ls.XtraB
   ...> );
9

I still get 9.  I also want 10 to be part of the result.  I know it's no longer there, but I would also like to have it as part of the result because this part of the select cl.XtraB != ls.XtraB matches.  Any easy way to do this?  In other words, when I run the SELECT, I want the the items that match the SELECT and any items that are not in the ALL table.  Thanks.

josé

_______________________________________________
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: Missing data on SELECT

Keith Medcalf

I think your query is in error.  Amongst other things, tou have the same condition listed twice:

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND                 <<<< DUPLICATE
   cl.XtraB != ls.XtraB
);

You cannot return a result which does not exist, and if you delete id=10 from the ProjectsALL table there will be no project in either table that has id=10.  How do you expect to return an id of 10 when that id does no exist?

What exactly, in English, is it that you are trying to achieve (ie, what is the PROBLEM STATEMENT for which you are trying to find a solution)?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of jose isaias cabrera
>Sent: Thursday, 2 November, 2017 16:02
>To: SQLite mailing list
>Subject: [sqlite] Missing data on SELECT
>
>
>Greetings.
>
>Apologies for the long email, or long set of data.  I have these two
>tables in two different database files, but for easy setup, I have
>place them in the same DB.  Having these data,
>
>
>CREATE TABLE ProjectsALL
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-05 10:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(10,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(11,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(12,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(13,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(14,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(15,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(16,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(17,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(18,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(19,'id0','A','ccc','2017-10-05 10:30:19');
>
>
>CREATE TABLE ProjectsMine
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-15 10:30:19');
>
>running this SELECT,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND
>   cl.XtraB != ls.XtraB
>);
>
>would give, 9.  Now let's delete record 10 on the ProjectsALL table,
>
>delete from ProjectsAll where id=10;
>
>after deleting record 10 and running the same SELECT above,
>
>sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>   ...> (
>   ...>    cl.id = ls.id AND
>   ...>    cl.login = 'id0' AND
>   ...>    cl.id = ls.id AND
>   ...>    cl.XtraB != ls.XtraB
>   ...> );
>9
>
>I still get 9.  I also want 10 to be part of the result.  I know it's
>no longer there, but I would also like to have it as part of the
>result because this part of the select cl.XtraB != ls.XtraB matches.
>Any easy way to do this?  In other words, when I run the SELECT, I
>want the the items that match the SELECT and any items that are not
>in the ALL table.  Thanks.
>
>josé
>
>_______________________________________________
>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: Missing data on SELECT

Jim Dodgen
You are inserting rows with ProjID but not the primary key "id"  which is
rowid and automatically created. I think it is not a good practice to
use rowid's like you are doing.  If you had inserted the id it would be a
different story.

*Jim Dodgen*







On Thu, Nov 2, 2017 at 5:26 PM, Keith Medcalf <[hidden email]> wrote:

>
> I think your query is in error.  Amongst other things, tou have the same
> condition listed twice:
>
> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> (
>    cl.id = ls.id AND
>    cl.login = 'id0' AND
>    cl.id = ls.id AND                 <<<< DUPLICATE
>    cl.XtraB != ls.XtraB
> );
>
> You cannot return a result which does not exist, and if you delete id=10
> from the ProjectsALL table there will be no project in either table that
> has id=10.  How do you expect to return an id of 10 when that id does no
> exist?
>
> What exactly, in English, is it that you are trying to achieve (ie, what
> is the PROBLEM STATEMENT for which you are trying to find a solution)?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of jose isaias cabrera
> >Sent: Thursday, 2 November, 2017 16:02
> >To: SQLite mailing list
> >Subject: [sqlite] Missing data on SELECT
> >
> >
> >Greetings.
> >
> >Apologies for the long email, or long set of data.  I have these two
> >tables in two different database files, but for easy setup, I have
> >place them in the same DB.  Having these data,
> >
> >
> >CREATE TABLE ProjectsALL
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(9,'id0','A','ccc','2017-10-05 10:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(10,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(11,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(12,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(13,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(14,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(15,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(16,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(17,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(18,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(19,'id0','A','ccc','2017-10-05 10:30:19');
> >
> >
> >CREATE TABLE ProjectsMine
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(9,'id0','A','ccc','2017-10-15 10:30:19');
> >
> >running this SELECT,
> >
> >SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> >(
> >   cl.id = ls.id AND
> >   cl.login = 'id0' AND
> >   cl.id = ls.id AND
> >   cl.XtraB != ls.XtraB
> >);
> >
> >would give, 9.  Now let's delete record 10 on the ProjectsALL table,
> >
> >delete from ProjectsAll where id=10;
> >
> >after deleting record 10 and running the same SELECT above,
> >
> >sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> >   ...> (
> >   ...>    cl.id = ls.id AND
> >   ...>    cl.login = 'id0' AND
> >   ...>    cl.id = ls.id AND
> >   ...>    cl.XtraB != ls.XtraB
> >   ...> );
> >9
> >
> >I still get 9.  I also want 10 to be part of the result.  I know it's
> >no longer there, but I would also like to have it as part of the
> >result because this part of the select cl.XtraB != ls.XtraB matches.
> >Any easy way to do this?  In other words, when I run the SELECT, I
> >want the the items that match the SELECT and any items that are not
> >in the ALL table.  Thanks.
> >
> >josé
> >
> >_______________________________________________
> >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: Missing data on SELECT

jose isaias cabrera-3
In reply to this post by Keith Medcalf

You're right.  Apologies.  The right SELECT would be,

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.ProjID > 3 AND
   cl.XtraB != ls.XtraB
);

To answer your question, and a long story made short, this is a SELECT to
see which items have changed between two DBs. id=10 existed in ProjectsALL,
but an error caused the deletion of id=10.  However, this id 10 lives in
another DB (ProjectsMine).  I would like the SELECT to tell me that  id=10
which exists in ProjectsMine, needs to be updated and PUSHED to ProjectsALL
because it does not exists there.  Maybe, I am going to have to do two
SELECTS, one that tells me which ProjID exists in ProjectsMine, but donot
exists in ProjectsAll.  These must be INSERTED into ProjectsALL.  And then
do the SELECT above to get which have changed the XtraB date.  I hope this
explains everything.  Thanks for the help.

-----Original Message-----
From: Keith Medcalf
Sent: Thursday, November 2, 2017 8:26 PM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT


I think your query is in error.  Amongst other things, tou have the same
condition listed twice:

SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.id = ls.id AND                 <<<< DUPLICATE
   cl.XtraB != ls.XtraB
);

You cannot return a result which does not exist, and if you delete id=10
from the ProjectsALL table there will be no project in either table that has
id=10.  How do you expect to return an id of 10 when that id does no exist?

What exactly, in English, is it that you are trying to achieve (ie, what is
the PROBLEM STATEMENT for which you are trying to find a solution)?

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of jose isaias cabrera
>Sent: Thursday, 2 November, 2017 16:02
>To: SQLite mailing list
>Subject: [sqlite] Missing data on SELECT
>
>
>Greetings.
>
>Apologies for the long email, or long set of data.  I have these two
>tables in two different database files, but for easy setup, I have
>place them in the same DB.  Having these data,
>
>
>CREATE TABLE ProjectsALL
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-05 10:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(10,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(11,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(12,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(13,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(14,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(15,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(16,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(17,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(18,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>(19,'id0','A','ccc','2017-10-05 10:30:19');
>
>
>CREATE TABLE ProjectsMine
>(
>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>);
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(1,'id0','A','aaa','2017-06-02 15:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(2,'id1','B','bbb','2017-06-03 12:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(3,'id2','A','ccc','2017-08-02 11:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(4,'id2','A','ccc','2017-09-02 17:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(5,'id3','A','ccc','2017-10-02 19:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(6,'id1','C','ccc','2017-10-02 18:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(7,'id1','C','ccc','2017-10-03 13:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(8,'id3','A','ccc','2017-10-04 14:30:19');
>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>(9,'id0','A','ccc','2017-10-15 10:30:19');
>
>running this SELECT,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND
>   cl.XtraB != ls.XtraB
>);
>
>would give, 9.  Now let's delete record 10 on the ProjectsALL table,
>
>delete from ProjectsAll where id=10;
>
>after deleting record 10 and running the same SELECT above,
>
>sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>   ...> (
>   ...>    cl.id = ls.id AND
>   ...>    cl.login = 'id0' AND
>   ...>    cl.id = ls.id AND
>   ...>    cl.XtraB != ls.XtraB
>   ...> );
>9
>
>I still get 9.  I also want 10 to be part of the result.  I know it's
>no longer there, but I would also like to have it as part of the
>result because this part of the select cl.XtraB != ls.XtraB matches.
>Any easy way to do this?  In other words, when I run the SELECT, I
>want the the items that match the SELECT and any items that are not
>in the ALL table.  Thanks.
>
>josé
>
>_______________________________________________
>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: Missing data on SELECT

jose isaias cabrera-3
In reply to this post by Jim Dodgen

It's a long story, but what I would like to SELECT is the id or ids that
have different XTraB between ProjectsMine and ProjectsALL, and also list the
id, or ids, that exists in ProjectsMine and not on ProjectsALL. This is do
do a push to ProjectsAll of those ids from ProjectsMine.

-----Original Message-----
From: Jim Dodgen
Sent: Thursday, November 2, 2017 11:43 PM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT

You are inserting rows with ProjID but not the primary key "id"  which is
rowid and automatically created. I think it is not a good practice to
use rowid's like you are doing.  If you had inserted the id it would be a
different story.

*Jim Dodgen*







On Thu, Nov 2, 2017 at 5:26 PM, Keith Medcalf <[hidden email]> wrote:

>
> I think your query is in error.  Amongst other things, tou have the same
> condition listed twice:
>
> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> (
>    cl.id = ls.id AND
>    cl.login = 'id0' AND
>    cl.id = ls.id AND                 <<<< DUPLICATE
>    cl.XtraB != ls.XtraB
> );
>
> You cannot return a result which does not exist, and if you delete id=10
> from the ProjectsALL table there will be no project in either table that
> has id=10.  How do you expect to return an id of 10 when that id does no
> exist?
>
> What exactly, in English, is it that you are trying to achieve (ie, what
> is the PROBLEM STATEMENT for which you are trying to find a solution)?
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of jose isaias cabrera
> >Sent: Thursday, 2 November, 2017 16:02
> >To: SQLite mailing list
> >Subject: [sqlite] Missing data on SELECT
> >
> >
> >Greetings.
> >
> >Apologies for the long email, or long set of data.  I have these two
> >tables in two different database files, but for easy setup, I have
> >place them in the same DB.  Having these data,
> >
> >
> >CREATE TABLE ProjectsALL
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(9,'id0','A','ccc','2017-10-05 10:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(10,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(11,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(12,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(13,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(14,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(15,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(16,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(17,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(18,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
> >(19,'id0','A','ccc','2017-10-05 10:30:19');
> >
> >
> >CREATE TABLE ProjectsMine
> >(
> >  id integer primary key, ProjID integer, login, cust, proj, XtraB
> >);
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(1,'id0','A','aaa','2017-06-02 15:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(2,'id1','B','bbb','2017-06-03 12:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(3,'id2','A','ccc','2017-08-02 11:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(4,'id2','A','ccc','2017-09-02 17:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(5,'id3','A','ccc','2017-10-02 19:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(6,'id1','C','ccc','2017-10-02 18:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(7,'id1','C','ccc','2017-10-03 13:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(8,'id3','A','ccc','2017-10-04 14:30:19');
> >INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
> >(9,'id0','A','ccc','2017-10-15 10:30:19');
> >
> >running this SELECT,
> >
> >SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> >(
> >   cl.id = ls.id AND
> >   cl.login = 'id0' AND
> >   cl.id = ls.id AND
> >   cl.XtraB != ls.XtraB
> >);
> >
> >would give, 9.  Now let's delete record 10 on the ProjectsALL table,
> >
> >delete from ProjectsAll where id=10;
> >
> >after deleting record 10 and running the same SELECT above,
> >
> >sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
> >   ...> (
> >   ...>    cl.id = ls.id AND
> >   ...>    cl.login = 'id0' AND
> >   ...>    cl.id = ls.id AND
> >   ...>    cl.XtraB != ls.XtraB
> >   ...> );
> >9
> >
> >I still get 9.  I also want 10 to be part of the result.  I know it's
> >no longer there, but I would also like to have it as part of the
> >result because this part of the select cl.XtraB != ls.XtraB matches.
> >Any easy way to do this?  In other words, when I run the SELECT, I
> >want the the items that match the SELECT and any items that are not
> >in the ALL table.  Thanks.
> >
> >josé
> >
> >_______________________________________________
> >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: Missing data on SELECT

Simon Slavin-3


On 3 Nov 2017, at 4:32am, jose isaias cabrera <[hidden email]> wrote:

> It's a long story, but what I would like to SELECT is the id or ids that have different XTraB between ProjectsMine and ProjectsALL, and also list the id, or ids, that exists in ProjectsMine and not on ProjectsALL.

Do it as two separate SELECTs.  You can use UNION to merge the results if you want, but these are really two separate things that can be dealt with in two steps.

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: Missing data on SELECT

Keith Medcalf
In reply to this post by jose isaias cabrera-3

Well that is a totally different thing.  Of course, in your sample data id-10 DOES NOT exist in ProjectMine, so you will never see id=10 no matter what you do.

select id from ProjectsMine where id not in (select id from ProjectsALL);

or

select id from ProjectsMine
except
select id from ProjectsAll;



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of jose isaias cabrera
>Sent: Thursday, 2 November, 2017 22:26
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>You're right.  Apologies.  The right SELECT would be,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.ProjID > 3 AND
>   cl.XtraB != ls.XtraB
>);
>
>To answer your question, and a long story made short, this is a
>SELECT to
>see which items have changed between two DBs. id=10 existed in
>ProjectsALL,
>but an error caused the deletion of id=10.  However, this id 10 lives
>in
>another DB (ProjectsMine).  I would like the SELECT to tell me that
>id=10
>which exists in ProjectsMine, needs to be updated and PUSHED to
>ProjectsALL
>because it does not exists there.  Maybe, I am going to have to do
>two
>SELECTS, one that tells me which ProjID exists in ProjectsMine, but
>donot
>exists in ProjectsAll.  These must be INSERTED into ProjectsALL.  And
>then
>do the SELECT above to get which have changed the XtraB date.  I hope
>this
>explains everything.  Thanks for the help.
>
>-----Original Message-----
>From: Keith Medcalf
>Sent: Thursday, November 2, 2017 8:26 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>I think your query is in error.  Amongst other things, tou have the
>same
>condition listed twice:
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND                 <<<< DUPLICATE
>   cl.XtraB != ls.XtraB
>);
>
>You cannot return a result which does not exist, and if you delete
>id=10
>from the ProjectsALL table there will be no project in either table
>that has
>id=10.  How do you expect to return an id of 10 when that id does no
>exist?
>
>What exactly, in English, is it that you are trying to achieve (ie,
>what is
>the PROBLEM STATEMENT for which you are trying to find a solution)?
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a
>lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of jose isaias cabrera
>>Sent: Thursday, 2 November, 2017 16:02
>>To: SQLite mailing list
>>Subject: [sqlite] Missing data on SELECT
>>
>>
>>Greetings.
>>
>>Apologies for the long email, or long set of data.  I have these two
>>tables in two different database files, but for easy setup, I have
>>place them in the same DB.  Having these data,
>>
>>
>>CREATE TABLE ProjectsALL
>>(
>>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>>);
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(1,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(2,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(3,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(4,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(5,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(6,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(7,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(8,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(9,'id0','A','ccc','2017-10-05 10:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(10,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(11,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(12,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(13,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(14,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(15,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(16,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(17,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(18,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(19,'id0','A','ccc','2017-10-05 10:30:19');
>>
>>
>>CREATE TABLE ProjectsMine
>>(
>>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>>);
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(1,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(2,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(3,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(4,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(5,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(6,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(7,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(8,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(9,'id0','A','ccc','2017-10-15 10:30:19');
>>
>>running this SELECT,
>>
>>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>>(
>>   cl.id = ls.id AND
>>   cl.login = 'id0' AND
>>   cl.id = ls.id AND
>>   cl.XtraB != ls.XtraB
>>);
>>
>>would give, 9.  Now let's delete record 10 on the ProjectsALL table,
>>
>>delete from ProjectsAll where id=10;
>>
>>after deleting record 10 and running the same SELECT above,
>>
>>sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>>   ...> (
>>   ...>    cl.id = ls.id AND
>>   ...>    cl.login = 'id0' AND
>>   ...>    cl.id = ls.id AND
>>   ...>    cl.XtraB != ls.XtraB
>>   ...> );
>>9
>>
>>I still get 9.  I also want 10 to be part of the result.  I know
>it's
>>no longer there, but I would also like to have it as part of the
>>result because this part of the select cl.XtraB != ls.XtraB matches.
>>Any easy way to do this?  In other words, when I run the SELECT, I
>>want the the items that match the SELECT and any items that are not
>>in the ALL table.  Thanks.
>>
>>josé
>>
>>_______________________________________________
>>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: Missing data on SELECT

jose isaias cabrera-3
In reply to this post by Simon Slavin-3

Thanks.

-----Original Message-----
From: Simon Slavin
Sent: Friday, November 3, 2017 12:40 AM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT



On 3 Nov 2017, at 4:32am, jose isaias cabrera <[hidden email]> wrote:

> It's a long story, but what I would like to SELECT is the id or ids that
> have different XTraB between ProjectsMine and ProjectsALL, and also list
> the id, or ids, that exists in ProjectsMine and not on ProjectsALL.

Do it as two separate SELECTs.  You can use UNION to merge the results if
you want, but these are really two separate things that can be dealt with in
two steps.

Simon.

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

Thanks.

_______________________________________________
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: Missing data on SELECT

jose isaias cabrera-3
In reply to this post by Keith Medcalf

This select works,

SELECT cl.id FROM ProjectsMine cl JOIN ProjectsALL ls ON
(
   cl.id = ls.id AND
   cl.login = 'id0' AND
   cl.ProjID > 3 AND
   cl.XtraB != ls.XtraB
)
UNION ALL
select id from ProjectsMine where id not in (select id from ProjectsALL);

thanks for the help, folks.


-----Original Message-----
From: Keith Medcalf
Sent: Friday, November 3, 2017 12:49 AM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT


Well that is a totally different thing.  Of course, in your sample data
id-10 DOES NOT exist in ProjectMine, so you will never see id=10 no matter
what you do.

select id from ProjectsMine where id not in (select id from ProjectsALL);

or

select id from ProjectsMine
except
select id from ProjectsAll;



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of jose isaias cabrera
>Sent: Thursday, 2 November, 2017 22:26
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>You're right.  Apologies.  The right SELECT would be,
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.ProjID > 3 AND
>   cl.XtraB != ls.XtraB
>);
>
>To answer your question, and a long story made short, this is a
>SELECT to
>see which items have changed between two DBs. id=10 existed in
>ProjectsALL,
>but an error caused the deletion of id=10.  However, this id 10 lives
>in
>another DB (ProjectsMine).  I would like the SELECT to tell me that
>id=10
>which exists in ProjectsMine, needs to be updated and PUSHED to
>ProjectsALL
>because it does not exists there.  Maybe, I am going to have to do
>two
>SELECTS, one that tells me which ProjID exists in ProjectsMine, but
>donot
>exists in ProjectsAll.  These must be INSERTED into ProjectsALL.  And
>then
>do the SELECT above to get which have changed the XtraB date.  I hope
>this
>explains everything.  Thanks for the help.
>
>-----Original Message-----
>From: Keith Medcalf
>Sent: Thursday, November 2, 2017 8:26 PM
>To: SQLite mailing list
>Subject: Re: [sqlite] Missing data on SELECT
>
>
>I think your query is in error.  Amongst other things, tou have the
>same
>condition listed twice:
>
>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>(
>   cl.id = ls.id AND
>   cl.login = 'id0' AND
>   cl.id = ls.id AND                 <<<< DUPLICATE
>   cl.XtraB != ls.XtraB
>);
>
>You cannot return a result which does not exist, and if you delete
>id=10
>from the ProjectsALL table there will be no project in either table
>that has
>id=10.  How do you expect to return an id of 10 when that id does no
>exist?
>
>What exactly, in English, is it that you are trying to achieve (ie,
>what is
>the PROBLEM STATEMENT for which you are trying to find a solution)?
>
>---
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a
>lot about anticipated traffic volume.
>
>
>>-----Original Message-----
>>From: sqlite-users [mailto:sqlite-users-
>>[hidden email]] On Behalf Of jose isaias cabrera
>>Sent: Thursday, 2 November, 2017 16:02
>>To: SQLite mailing list
>>Subject: [sqlite] Missing data on SELECT
>>
>>
>>Greetings.
>>
>>Apologies for the long email, or long set of data.  I have these two
>>tables in two different database files, but for easy setup, I have
>>place them in the same DB.  Having these data,
>>
>>
>>CREATE TABLE ProjectsALL
>>(
>>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>>);
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(1,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(2,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(3,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(4,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(5,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(6,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(7,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(8,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(9,'id0','A','ccc','2017-10-05 10:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(10,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(11,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(12,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(13,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(14,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(15,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(16,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(17,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(18,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsALL(ProjID, login, cust, proj, XtraB) VALUES
>>(19,'id0','A','ccc','2017-10-05 10:30:19');
>>
>>
>>CREATE TABLE ProjectsMine
>>(
>>  id integer primary key, ProjID integer, login, cust, proj, XtraB
>>);
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(1,'id0','A','aaa','2017-06-02 15:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(2,'id1','B','bbb','2017-06-03 12:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(3,'id2','A','ccc','2017-08-02 11:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(4,'id2','A','ccc','2017-09-02 17:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(5,'id3','A','ccc','2017-10-02 19:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(6,'id1','C','ccc','2017-10-02 18:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(7,'id1','C','ccc','2017-10-03 13:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(8,'id3','A','ccc','2017-10-04 14:30:19');
>>INSERT INTO ProjectsMine (ProjID, login, cust, proj, XtraB) VALUES
>>(9,'id0','A','ccc','2017-10-15 10:30:19');
>>
>>running this SELECT,
>>
>>SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>>(
>>   cl.id = ls.id AND
>>   cl.login = 'id0' AND
>>   cl.id = ls.id AND
>>   cl.XtraB != ls.XtraB
>>);
>>
>>would give, 9.  Now let's delete record 10 on the ProjectsALL table,
>>
>>delete from ProjectsAll where id=10;
>>
>>after deleting record 10 and running the same SELECT above,
>>
>>sqlite> SELECT cl.ProjID FROM ProjectsMine cl JOIN ProjectsALL ls ON
>>   ...> (
>>   ...>    cl.id = ls.id AND
>>   ...>    cl.login = 'id0' AND
>>   ...>    cl.id = ls.id AND
>>   ...>    cl.XtraB != ls.XtraB
>>   ...> );
>>9
>>
>>I still get 9.  I also want 10 to be part of the result.  I know
>it's
>>no longer there, but I would also like to have it as part of the
>>result because this part of the select cl.XtraB != ls.XtraB matches.
>>Any easy way to do this?  In other words, when I run the SELECT, I
>>want the the items that match the SELECT and any items that are not
>>in the ALL table.  Thanks.
>>
>>josé
>>
>>_______________________________________________
>>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


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