SQLite Forum

Subqueries and "no such column" error on references to other tables
Login
Columns from both of the join operands can be referenced in ON clause - thus your first query works.
But your second query is trying to refer to first join operand’s column in second join operand - that is not allowed.