Hi, On Fri, Apr 5, 2019 at 1:36 PM Jose Isaias Cabrera <[email protected]> wrote: > > > Thanks, Simon. Works like a charm...
Unless backwards compatibility is important (do you expect to go back to pre-foreign keys implementation), I'd do FOREIGN KEY amd forget anout that... Thank you. > > > From: Simon Davies > Sent: Friday, April 5, 2019 12:24 PM > To: SQLite mailing list > Subject: Re: [sqlite] Returning NULL or empty values when the SELECT does not > satisfy all of the query > > On Fri, 5 Apr 2019 at 14:45, Jose Isaias Cabrera <[email protected]> wrote: > > > > Greetings. > > > > I have a few tables that I am bringing data from, but I found a bug in my > > logic, which I am trying to see if I can make it work. Please look at this > > scenario > > > > create table t (n INTEGER PRIMARY KEY, a, b, c, d, e, idate); > > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4, > > '2019-02-11'); > > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4, > > '2019-02-11'); > > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4, > > '2019-02-11'); > > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4, > > '2019-02-11'); > > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4, > > '2019-02-11'); > > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4, > > '2019-02-12'); > > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4, > > '2019-02-12'); > > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4, > > '2019-02-12'); > > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4, > > '2019-02-12'); > > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4, > > '2019-02-12'); > > insert into t (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4, > > '2019-02-13'); > > insert into t (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4, > > '2019-02-13'); > > insert into t (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4, > > '2019-02-13'); > > insert into t (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4, > > '2019-02-13'); > > insert into t (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4, > > '2019-02-13'); > > > > select * from t; > > > > create table z (n INTEGER PRIMARY KEY, f, g, h, i, j, idate); > > insert into z (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4, > > '2019-02-15'); > > insert into z (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4, > > '2019-02-15'); > > insert into z (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4, > > '2019-02-15'); > > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4, > > '2019-02-16'); > > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4, > > '2019-02-16'); > > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4, > > '2019-02-17'); > > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4, > > '2019-02-17'); > > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4, > > '2019-02-17'); > > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4, > > '2019-02-17'); > > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4, > > '2019-02-18'); > > insert into z (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4, > > '2019-02-18'); > > insert into z (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4, > > '2019-02-18'); > > insert into z (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4, > > '2019-02-18'); > > insert into z (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4, > > '2019-02-18'); > > insert into z (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4, > > '2019-02-18'); > > > > select * from z; > > > > I can do this, > > > > select > > a.*, b.* from t as a join z as b on a.a = b.f > > where a.a = 'p001' > > AND > > a.idate = (select max(idate) from t where a = a.a) > > AND > > b.idate = (select max(idate) from z where f = a.a) > > ORDER BY a.a > > ; > > > > and get the correct output, > > > > 11|p001|a|3|n|4|2019-02-13|11|p001|a|3|a|4|2019-02-18 > > > > without any problem. But, when I do this, > > > > insert into t (a, b, c, d, e, idate) values ('p006', 'e', 8, 'n', 5, > > '2019-03-01'); > > > > and then do this, > > > > select > > a.*, b.* from t as a join z as b on a.a = b.f > > where a.a = 'p006' > > AND > > a.idate = (select max(idate) from t where a = a.a) > > AND > > b.idate = (select max(idate) from z where f = a.a) > > ORDER BY a.a > > ; > > > > I get nothing. I know why, but what will save my logic is, to be able to > > fix the query above and get something like this, > > > > 16|p006|e|8|n|5|2019-03-01||||||| > > > > in other words, NULL values instead. This will fix my "logic". :-) and the > > world will be at peace again. :-) Is this even possible? Thanks. > > left join: > > select > a.*, b.* from t as a left join z as b on a.a = b.f > AND > a.idate = (select max(idate) from t where a = a.a) > AND > b.idate = (select max(idate) from z where f = a.a) > where a.a = 'p006' > ORDER BY a.a > ; > > > josé > > Regards, > Simon > _______________________________________________ > 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
