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
