SQLite subquery syntax/error/difference from MySQL

Question

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.

Answer 1

It works in MySQL:

CREATE TABLE payments (bill_year INT, bill_month INT);
CREATE TABLE target_money (amount INT, start_year INT, start_month INT);

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;

I'd imagine that it would work in SQLite too. I'm sure someone here can copy & paste the above to verify it...

Answer 2

Thanks, Mark.
Your query works fine in SQLite:

>>> import sqlite3
>>> conn = sqlite3.connect(':memory:')
>>> c = conn.cursor()

>>> c.execute('CREATE TABLE payments (bill_year INT, bill_month INT);')
<sqlite3.Cursor object at 0x00C62CE0>
>>> conn.commit()

>>> c.execute("""CREATE TABLE target_money 
        (amount INT, start_year INT, start_month INT);""")
<sqlite3.Cursor object at 0x00C62CE0>
>>> conn.commit()

>>> c.execute("""
... 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;
... """)
<sqlite3.Cursor object at 0x00C62CE0>
>>> c.fetchall()
[]