i have query
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;
and the result is
1054 - Unknown column 't1.id' in 'where clause', Time: 0.001000s
how to access t1.id current subquery select ?
You may try just using a join to a single level subquery on orders:
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;