[sqlite] Missing data on SELECT

jose isaias cabrera Thu, 02 Nov 2017 15:02:59 -0700

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

Reply via email to