This is the closest thing I've come to a working query is where the error thrown is saying the first instance of thread.node_id doesn't exist...
'LEFT JOIN ( (SELECT mt1.field1, mt1.field2 FROM xf_mytable AS mt1 WHERE mt1.node_id = thread.node_id) UNION (SELECT mt2.field1, mt2.field2 FROM xf_mytable AS mt2 WHERE mt2.field5 = other_result.field5) ) AS aggregate_result ON (aggregate_result.node_id = thread.node_id)';
The reason for the union is this left join is causing a monstrous load on MySql on a big board. I'm hoping a union will cure the load problem.
'LEFT JOIN xf_mytable AS mt1 ON (mt1.node_id = thread.node_id OR mt1.field5 = other_result.field5)'