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.
posted by le morte de bea arthur to computers & internet (14 answers total)
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...