To make outer query column available to nested query

Question

Getting the error

Error Code: 1054. Unknown column 'tb.CHECKOUTID' in 'where clause'.

I am unable to access the column of outer query.

SELECT
    (
        SELECT
            SUM(x.chrg)
        FROM
            (
                SELECT
                    SUM(charge_amount)
                FROM
                    ys_charges AS fc
                WHERE
                    fc.CHECKOUTID = tb.CHECKOUTID

                UNION

                SELECT
                    SUM(fc.charge_amount)
                FROM
                    ys_charges AS fc
                LEFT JOIN ys_charges AS fc2
                    ON fc2.CHECKOUTID = fc2.CHECKOUTID
                WHERE
                    fc.CHECKOUTID = tb.CHECKOUTID
            ) AS x
    ) AS yval
FROM
    tbl AS tb
WHERE
    tb.ID= '285936'
LIMIT 1

Answer 1

You can correlate with the value which is retrieved from adjacent nesting level, but you cannot correlate through the nesting level.

You may try to add a rowsource (subquery) into the intermediate level which retrieves according data with correlated SELECT. Must work... test.

SELECT ( SELECT ...
         FROM ( SELECT ... 
                FROM ... 
                WHERE fc.CHECKOUTID = y.CHECKOUTID  -- use value from 1st nesting level
                ) AS x
         CROSS JOIN (SELECT tb.CHECKOUTID) AS y     -- copy from outer query to 1st nesting level
                                                    -- which makes it available on 2nd nesting level
         ) AS yval 
FROM tb
...