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.

josé

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

Reply via email to