SQLite Forum

Not understanding a subquery - Some guidance?
`t` in `where t.ritm = r.ritm` refers to the first (leftmost) occurrence of the name `t` - that is, the outer `t`, not the inner `t`. The inner select is a correlated subquery, but not in the way you think; for each pair of rows in `r` and `t`, it devolves to either `select ritm from t where true ...` or `select ritm from t where false ...`. As a result, I believe the query is equivalent simply to `select * from r outer join t on (r.ritm = t.ritm)`

You probably meant something like this (not tested):

select * from r
left outer join t t1 on t1.task=
(select task from t t2 where t2.ritm = r.ritm order by date(ud) desc limit 1);