Sorry to bother you with this simple request, but I can't seem to come up with
a solution. Imagine these tables:
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');
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');
What I am trying to do is to SELECT the max(idate) items a, b and c from t and
g, h, and i from z where t.a and z.f = 'p001' in both tables. So, what I want
is,
'p001', 'a', 3, 'a', 3, 'a'
from the combination of both <-----. So, here is what I have tried,
select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b on a.a = b.f
where a.a = 'p001'
group by a.a
having
( a.idate = (select max(a.idate) from t as c where a.n = c.n)
and
b.idate = (select max(b.idate) from z as d where b.n = d.n)
);
But, I get nothing.
sqlite> select a.a, a.b, a.c, b.g, b.h, b.i from t as a join z as b on a.a = b.f
...> where a.a = 'p001'
...> group by a.a
...> having
...> ( a.idate = (select max(a.idate) from t as c where a.n = c.n)
...> and
...> b.idate = (select max(b.idate) from z as d where b.n = d.n)
...> );
sqlite>
Any help would be greatly appreciated. Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
