Passing a value to a nested subquery

Question

I have the following query (stripped down for brevity), the aim of which is to create the w8 value used to order the results:

SELECT elements.id, [ ... ],
(SELECT 
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0) 
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b` 
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = elements.id)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

The problem I'm having is that the second nested subquery (the one in the left join) is unable to find the column elements.id from the initial select.

From what I've found searching around SQL only passes values one level deep, and I've been unable to find a suitable work-around.

Is it possible to force SQL to pass a value deeper than one level? Or is there a way of modifying the query to not use another subquery, but still get the same result?

Sorry if I'm doing anything stupid or missed anything obvious, SQL is not my strong point!

Answer 1

To my knowledge, this limitation pertains to SQL specifically in MySQL. All the other SQL products that I am aware of do not have it.

There does not appear to be a solution to this problem in general: you cannot force a column reference to be recognised deeper than one level in MySQL. However, there is an easy workaround in your specific case: replace the elements.id in the innermost query with craft_w8_a.elementId:

SELECT elements.id, [ ... ],
(SELECT 
    COALESCE(craft_w8_a.weight, 0) + COALESCE(SUM(craft_w8_b.weight), 0) 
    FROM `craft_w8` `craft_w8_a`
    LEFT JOIN `craft_w8` `craft_w8_b` 
        ON craft_w8_b.elementId
        IN ( SELECT targetId FROM `craft_relations`
                WHERE fieldId IN (15, 16)
                  AND sourceId = craft_w8_a.elementId)
    WHERE craft_w8_a.elementId = elements.id
) as w8
FROM `craft_elements` `elements`
[ ... ]
GROUP BY `elements`.`id`
ORDER BY `w8` DESC, `name` ASC LIMIT 100

This is an equivalent substitute because whatever craft_w8_a.elementId will be passed to the IN subquery is going to match elements.id based on the filter in the query that passes the value.