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 <kmedc...@dessus.com> 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-
> >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

Reply via email to