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!