select a, b, c, g, h, i from t outer_t, z outer_z where a == f and a == 'p001' and outer_t.idate == (select max(idate) from t where a == outer_t.a) and outer_z.idate == (select max(idate) from z where f == outer_z.f) ;
This requires that t(a, idate) is unique and that z(f, idate) is unique (in which case you should declare it so in order to prevent violation of your requirements). If that assumption is incorrect then there is no solution (or more correctly that your schema is improperly normalized). --- 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: Tuesday, 26 February, 2019 20:09 >To: sqlite-users@mailinglists.sqlite.org >Subject: [sqlite] Getting data from two JOIN tables > > >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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
