SQLite subquery syntax / error / difference from MySQL
I was under the impression that this is the 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 is not, because SQLite is returning this error:
no such column: p.bill_year
What's wrong with the way I access p.bill_year?
Yes, I'm sure the table payments
contains a column bill_year
. Am I crazy or is this just valid SQL syntax? This will work in MySQL, won't it? I don't have any other SQL views, so I can't test others, but I thought SQLite was pretty standard.
a source to share
Thank you 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()
[]
a source to share
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 would guess it will work in SQLite too. I'm sure someone here can copy and paste the above to test it ...
a source to share
I am doing some tests to confirm that correlated subqueries do not work on sqlite2 but do work on sqlite3 and this seems to be the case. The problem is that the official documentation doesn't say anything about it. There is still a small chance that correlated subqueries are supported in sqlite2 with some odd syntax. That's all I can say without getting into the sqlite2 source code.
a source to share