David Raymond, on Monday, January 27, 2020 10:32 AM, wrote...
[clip]
> (c.WYear = 2020) is a perfectly valid expression... that's returning a
> boolean (well, int)
> So you're comparing c.WYear (from the subquery) against a boolean.
Yep, this little bit I knew. :-)
> (Others have replied with improved versions of the query, but for people
> following at home I figured I'd try to point out why the original version
> parsed ok and ran, just wasn't what you intended)
Fair enough...
The original email had inserts that would suffice the table that would create
the 'YES' or the 'NO'. For example...
create table t0 (n INTEGER PRIMARY KEY, a, b, c, d, e, idate);
insert into t0 (a, b, c, d, e, idate) values ('p001', 1, 2019, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'n', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
'2019-02-11');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2019, 'y', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p004', 4, 2019, 'y', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2019, 'y', 4,
'2019-02-12');
insert into t0 (a, b, c, d, e, idate) values ('p001', 10, 2020, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p002', 2, 2019, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p003', 3, 2020, 'y', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p004', 5, 2019, 'n', 4,
'2019-02-13');
insert into t0 (a, b, c, d, e, idate) values ('p005', 5, 2020, 'y', 8,
'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', 1, 4, 'n', 4,
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 3, 'n', 4,
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 2, 'n', 4,
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 5, 'y', 4,
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 4,
'2019-02-11');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 2, 'n', 4,
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 6, 'n', 4,
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 7, 'y', 4,
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p004', 4, 1, 'y', 4,
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 2, 'y', 4,
'2019-02-12');
insert into t1 (f, g, h, i, j, idate) values ('p001', 10, 1, 'n', 4,
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p002', 2, 8, 'n', 4,
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p003', 3, 9, 'y', 4,
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p004', 5, 3, 'n', 4,
'2019-02-13');
insert into t1 (f, g, h, i, j, idate) values ('p005', 5, 3, 'y', 8,
'2019-02-13');
create table t2 (pid, WYear);
insert into t2 values ('p001', 2019);
insert into t2 values ('p003', 2019);
insert into t2 values ('p004', 2019);
insert into t2 values ('p002', 2020);
insert into t2 values ('p003', 2020);
insert into t2 values ('p005', 2020);
As you can see, t2 contains data matches well with t0 and t1 regarding a, f and
pid. However, when I ran this on the real data, I found out that that there
was data missing, ie.
insert into t0 (a, b, c, d, e, idate) values ('p006', 5, 2020, 'y', 8,
'2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p007', 5, 2020, 'n', 8,
'2019-03-13');
insert into t0 (a, b, c, d, e, idate) values ('p008', 5, 2020, 'n', 8,
'2019-03-13');
and
insert into t1 (f, g, h, i, j, idate) values ('p006', 6, 7, 'n', 8,
'2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p007', 6, 7, 'n', 8,
'2019-03-13');
insert into t1 (f, g, h, i, j, idate) values ('p008', 6, 9, 'y', 8,
'2019-03-13');
So, when I ran the original query,
SELECT a.a,
a.c,
a.e,
b.g,
b.h,
b.i,
coalesce((
SELECT 'YES'
FROM t2
WHERE wYear == a.c
AND pid == a.a
), 'NO') AS digital
FROM t0 as a, t1 as b
WHERE a.a == b.f
AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
AND a.a IN (SELECT pid FROM t2)
AND a.c == 2020
;
it would only give me the records that were part of t2:
p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES
But, I also needed to display p006, p007, p008. So, by taking the third to
last line, "AND a.a IN (SELECT pid FROM t2)", that gave me the correct result:
sqlite> SELECT a.a,
...> a.c,
...> a.e,
...> b.g,
...> b.h,
...> b.i,
...> coalesce((
...> SELECT 'YES'
...> FROM t2
...> WHERE wYear == a.c
...> AND pid == a.a
...> ), 'NO') AS digital
...> FROM t0 as a, t1 as b
...> WHERE a.a == b.f
...> AND a.idate == (SELECT MAX(idate) from t0 where a = a.a)
...> AND b.idate == (SELECT MAX(idate) from t1 where f = a.a)
...> AND a.c == 2020
...> ;
p001|2020|4|10|1|n|NO
p003|2020|4|3|9|y|YES
p005|2020|8|5|3|y|YES
p006|2020|8|6|7|n|NO
p007|2020|8|6|7|n|NO
p008|2020|8|6|9|y|NO
sqlite>
And that's it... :-) For those of you scoring at home:
Jose request for SQL help: 2031
Jose able to figure his own problem: 23
:-)
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
