SQL JOINs are driving me crazy
September 1, 2010 7:55 AM Subscribe
Help me dig myself out of an SQL hole involving crazy multiple joins.
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.id AS r3_id, r2.id AS r2_id, r1.id AS r1_id
LEFT JOIN (
INNER JOIN (
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, but this is starting to go beyond my SQL comfort zone, and I'm getting confused.
So please help, O clear-headed gods of complicated multiple-self-joins...