CODEWITHSUNDEEP
mysqlmariadb
Rizal Iman Muttaqin
Rizal Iman Muttaqin

Reputation: 33

How to acces outer column in mysql subquery?

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 ?

Upvotes: 1

Views: 71

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521178

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;

Upvotes: 3

Related Questions