tengo consulta
SELECT t1.account AS main, t1.id, tmp.* FROM account.account t1 LEFT JOIN ( SELECT tmp1.account, tmp1.lot - tmp2.lot AS nett FROM ( SELECT account, SUM( lot ) AS lot FROM orders WHERE market_date = "2020-07-20" AND `transaction` = 1 AND account = t1.id ) AS tmp1 LEFT JOIN ( SELECT account, SUM( lot ) AS lot FROM orders WHERE market_date = "2020-07-20" AND `transaction` = 2 AND account = t1.id ) AS tmp2 ON tmp1.account = tmp2.account ) tmp ON tmp.account = t1.id;y el resultado es
1054 - Unknown column 't1.id' in 'where clause', Time: 0.001000s¿Cómo acceder a la selección de subconsulta actual t1.id?
Puede intentar simplemente usar una unión a una subconsulta de un solo nivel en los pedidos:
SELECT a.account AS main, a.id, COALESCE(o.nett, 0) AS nett FROM account a LEFT JOIN ( SELECT account, SUM(CASE WHEN `transaction` = 1 THEN lot END) - SUM(CASE WHEN `transaction` = 2 THEN lot END) AS nett FROM orders WHERE market_date = '2020-07-20' GROUP BY account ) o ON o.account = a.account;