SQLite subquery syntax/error/difference from MySQL
- by Rudie
I was under the impression this is valid SQLite syntax:
SELECT
*,
(SELECT amount AS target
FROM target_money
WHERE start_year <= p.bill_year
AND start_month <= p.bill_month
ORDER BY start_year ASC, start_month ASC
LIMIT 1) AS target
FROM payments AS p;
But I guess it's not, because SQLite returns this error:
no such column: p.bill_year
What's wrong with how I refer to p.bill_year?
Yes, I am positive table payments hosts a column bill_year. Am I crazy or is this just valid SQL syntax? It would work in MySQL wouldn't it?? I don't have any other SQL present so I can't test others, but I thought SQLite was quite standardlike.
