MySQL problem involving crazy multiple self-joins

Question

As part of the process of replacing some old code that used an incredibly slow nested select, I've ended up with a query that looks like this:

SELECT r3.r_id AS r3_id, r2.r_id AS r2_id, r1.r_id AS r1_id
FROM
table_r r3
LEFT JOIN (
  table_r r2
  INNER JOIN (
    table_r r1
    INNER JOIN table_d d ON r1.r_id = d.r_id
  ) ON r2.r_id = r1.parent_id
) ON r3.r_id = r2.r_id
WHERE d.d_id = 3

So in the innermost join, I'm looking for the records in table_r (copy r1) which have a relationship with a subset of records from table_d.

In the next join out, I'm looking for records in a second copy of table_r (r2) whose main index (r_id) matches the parent index (parent_id) of the records from the previous join.

Then I'm trying to do a LEFT JOIN with a third copy of table_r (r3), simply matching r_id with the r_id of the previous join. The idea of this outermost join is to get ALL of the records from table_r, but to then do the equivalent of a 'NOT IN' select by using a further condition (not yet in my query) to determine which records in r3 have NULLs for r2_id.

The problem is that the LEFT JOIN is not giving me the whole of table_r. It's giving me the same subset of records that I get without the final join - in other words, the same thing as an INNER JOIN. So whereas I'm expecting 1208 records, I get 508.

I know I must be doing something screwy here...

Answer 1

What happens, if you try this?

SELECT r3.r_id AS r3_id, r2.r_id AS r2_id, r1.r_id AS r1_id
FROM
table_r r3
LEFT JOIN (
  table_r r2
 INNER JOIN (
   table_r r1
   INNER JOIN table_d d ON r1.r_id = d.r_id AND d.d_id = 3
 ) ON r2.r_id = r1.parent_id
) ON r3.r_id = r2.r_id

What I did was moved the d.d_id = 3 from where clause to the INNER JOINs ON qualifiers.