Re: [sqlite] Missing data on SELECT

[jose isaias cabrera]

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-
boun...@mailinglists.sqlite.org] 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-
boun...@mailinglists.sqlite.org] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users