It's a long story, but what I would like to SELECT is the id or ids that have different XTraB between ProjectsMine and ProjectsALL, and also list the id, or ids, that exists in ProjectsMine and not on ProjectsALL. This is do do a push to ProjectsAll of those ids from ProjectsMine.


-----Original Message----- From: Jim Dodgen
Sent: Thursday, November 2, 2017 11:43 PM
To: SQLite mailing list
Subject: Re: [sqlite] Missing data on SELECT

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


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

Reply via email to