Left Join Union

[Snog]

I have a need for a left join query that includes a union. I've tried every iteration I can think of but none of them seem to work. This is in an extension for the thread model (fetchOptions['jointables'])

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)';

Any help would be greatly appreciated.

EDIT:

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)'

 

[Jake Bunce]

Subqueries as joins can't access outside members (thread.node_id) so that column will not be found on the inside. Also, your subquery isn't returning node_id so aggregate_result.node_id will be undefined in the ON clause on the outside.

'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)';

I don't want to suggest a fix until I have more information about the problem and the data. What are you trying to accomplish with the query? Which tables are involved and how big are they?

Inefficient joins might respond well to additional indexes on the tables in question.

 

[Snog]

It's while reading the thread table and joining my custom table.

The thread table has over 5 million threads and my custom table has over 2000 entries keyed on a previous join response (other_result.field5) that is indexed in my custom table (mt1.field5).

If I remove the 'OR' from this join, mysql load drops to normal..

'LEFT JOIN xf_mytable AS mt1 ON
(mt1.node_id = thread.node_id OR mt1.field5 = other_result.field5)'

The reason I was thinking UNION would work is because the result should combined into one response.

I believe I've solved the problem by using IF(other_result.field5,result1,result2) AS result and two LEFT JOINS. The timing on my test server dropped to near normal by doing that and I'm waiting for feedback from a test site to see if it works in a live environment.