This form of my correlated sub query comes up with the error message "Unknown column ‘Invoices.TranDate’ in ‘where clause’"
select InvoiceID, TranDate , ifnull(TotPayments,0) TotPayments, ifnull(CountPayments,0) CountPayments from Invoices left join (select DebtorID, sum(TranAmount) TotPayments, count(*) CountPayments from CashTrans where CashTrans.TranDate >= Invoices.TranDate group by DebtorID) PY on PY.DebtorID = Invoices.DebtorID
Yet this version works
select InvoiceID, TranDate , (select sum(TranAmount) from CashTrans where CashTrans.TranDate >= Invoices.TranDate and CashTrans.DebtorID = Invoices.DebtorID) TotPayments , (select count(*) from CashTrans where CashTrans.TranDate >= Invoices.TranDate and CashTrans.DebtorID = Invoices.DebtorID) CountPayments from Invoices;
What is wrong with the first query? The only thing I can think of is that on my Windows system I have configured
lower_case_table_names=2 as I want to preserve mixed case names. Perhaps that has something to do with the first query not seeing Invoice.TranDate in scope? MySQL Documentation and internet searches have not thrown any light on the matter.
A derived table cannot normally refer to (depend on) columns of preceding tables in the same FROM clause. As of MySQL 8.0.14, a derived table may be defined as a lateral derived table to specify that such references are permitted.
In SQL:1999, the query becomes legal if the derived tables are preceded by the LATERAL keyword (which means “this derived table depends on previous tables on its left side”):
I have not tested it, but I believe your query could be written this way:
SELECT InvoiceID, TranDate, IFNULL(TotPayments,0) AS TotPayments, ifnull(CountPayments,0) AS CountPayments FROM Invoices LEFT JOIN LATERAL ( SELECT DebtorID, SUM(TranAmount) AS TotPayments, COUNT(*) AS CountPayments FROM CashTrans WHERE CashTrans.TranDate >= Invoices.TranDate GROUP BY DebtorID ) AS PY ON PY.DebtorID = Invoices.DebtorID;
Also be aware this requires you to use at least MySQL 8.0.14.
Answered By – Bill Karwin
Answer Checked By – Robin (BugsFixing Admin)