Greetings.
Please take a look at the following:
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 1, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 1, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 2, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 2, 'y', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 3, 'y', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 4, 'n', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 5, 'n', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 6, 'n', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 7, 'y', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 8, 'y', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p001', 'a', 3, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p002', 'a', 4, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p003', 'a', 5, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p004', 'a', 6, 'y', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p005', 'a', 7, 'y', 4,
'2019-02-13');
create table t1 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into t1 (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
'2019-02-15');
insert into t1 (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
'2019-02-15');
insert into t1 (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
'2019-02-15');
insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
'2019-02-16');
insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
'2019-02-16');
insert into t1 (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
'2019-02-17');
insert into t1 (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
'2019-02-17');
insert into t1 (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
'2019-02-17');
insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
'2019-02-17');
insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
'2019-02-18');
insert into t1 (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
'2019-02-18');
insert into t1 (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
'2019-02-18');
insert into t1 (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
'2019-02-18');
insert into t1 (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
'2019-02-18');
insert into t1 (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
'2019-02-18');
create table t2 (n INTEGER PRIMARY KEY, f, g, h, i, j, idate);
insert into t2 (f, g, h, i, j, idate) values ('p001', 'b', 6, 'o', 4,
'2019-02-15');
insert into t2 (f, g, h, i, j, idate) values ('p002', 'b', 6, 'o', 4,
'2019-02-15');
insert into t2 (f, g, h, i, j, idate) values ('p003', 'b', 6, 'i', 4,
'2019-02-15');
insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 9, 'i', 4,
'2019-02-16');
insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 8, 'u', 4,
'2019-02-16');
insert into t2 (f, g, h, i, j, idate) values ('p001', 'a', 8, 'u', 4,
'2019-02-17');
insert into t2 (f, g, h, i, j, idate) values ('p002', 'a', 5, 'e', 4,
'2019-02-17');
insert into t2 (f, g, h, i, j, idate) values ('p003', 'a', 7, 'e', 4,
'2019-02-17');
insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 7, 'b', 4,
'2019-02-17');
insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 3, 'b', 4,
'2019-02-18');
insert into t2 (f, g, h, i, j, idate) values ('p001', 'a', 3, 'a', 4,
'2019-02-18');
insert into t2 (f, g, h, i, j, idate) values ('p002', 'a', 3, 'a', 4,
'2019-02-18');
insert into t2 (f, g, h, i, j, idate) values ('p003', 'a', 5, 'a', 4,
'2019-02-18');
insert into t2 (f, g, h, i, j, idate) values ('p004', 'a', 6, 'o', 4,
'2019-02-18');
insert into t2 (f, g, h, i, j, idate) values ('p005', 'a', 7, 'o', 4,
'2019-02-18');
sqlite> select * from t1;
1|p001|b|6|o|4|2019-02-15
2|p002|b|6|o|4|2019-02-15
3|p003|b|6|i|4|2019-02-15
4|p004|a|9|i|4|2019-02-16
5|p005|a|8|u|4|2019-02-16
6|p001|a|8|u|4|2019-02-17
7|p002|a|5|e|4|2019-02-17
8|p003|a|7|e|4|2019-02-17
9|p004|a|7|b|4|2019-02-17
10|p005|a|3|b|4|2019-02-18
11|p001|a|3|a|4|2019-02-18
12|p002|a|3|a|4|2019-02-18
13|p003|a|5|a|4|2019-02-18
14|p004|a|6|o|4|2019-02-18
15|p005|a|7|o|4|2019-02-18
sqlite> select * from t0;
1|p001|a|1|n|4|2019-02-11
2|p002|a|1|n|4|2019-02-11
3|p003|a|2|n|4|2019-02-11
4|p004|a|2|y|4|2019-02-11
5|p005|a|3|y|4|2019-02-11
6|p001|a|4|n|4|2019-02-12
7|p002|a|5|n|4|2019-02-12
8|p003|a|6|n|4|2019-02-12
9|p004|a|7|y|4|2019-02-12
10|p005|a|8|y|4|2019-02-12
11|p001|a|3|n|4|2019-02-13
12|p002|a|4|n|4|2019-02-13
13|p003|a|5|n|4|2019-02-13
14|p004|a|6|y|4|2019-02-13
15|p005|a|7|y|4|2019-02-13
sqlite> select * from t2;
1|p001|b|6|o|4|2019-02-15
2|p002|b|6|o|4|2019-02-15
3|p003|b|6|i|4|2019-02-15
4|p004|a|9|i|4|2019-02-16
5|p005|a|8|u|4|2019-02-16
6|p001|a|8|u|4|2019-02-17
7|p002|a|5|e|4|2019-02-17
8|p003|a|7|e|4|2019-02-17
9|p004|a|7|b|4|2019-02-17
10|p005|a|3|b|4|2019-02-18
11|p001|a|3|a|4|2019-02-18
12|p002|a|3|a|4|2019-02-18
13|p003|a|5|a|4|2019-02-18
14|p004|a|6|o|4|2019-02-18
15|p005|a|7|o|4|2019-02-18
sqlite> select count(a.n),count(b.n),count(c.n) FROM t0 AS a LEFT JOIN t1 AS b
LEFT JOIN t2 AS c;
3375|3375|3375
Huh? I expected the result:
15|15|15
Thoughts? Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
