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 <[email protected]> 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- > >[email protected]] 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 > >[email protected] > >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
