Left join on a table with condition on others table

What you need to do is the left outer joins from the b table to the c and d tables first, and then outer join that back to the a table if a value exists in either the c or d conditions columns. Like so:

SELECT a.id a_id, b2.b_id, b2.c_id, b2.d_idFROM   a       LEFT OUTER JOIN (SELECT b.id b_id,                               b.a_id,                               c.id c_id,                               d.id d_id                        FROM   b                               LEFT OUTER JOIN c ON b.c_id = c.id AND c.conditions = 1                               LEFT OUTER JOIN d ON b.d_id = d.id AND d.conditions = 1) b2         ON a.id = b2.a_id AND COALESCE(b2.c_id, b2.d_id) IS NOT NULLORDER BY a.id, b2.b_id, b2.c_id, b2.d_id;      A_ID       B_ID       C_ID       D_ID---------- ---------- ---------- ----------         1          1          1          1          2          2          2                                3                       

(Thanks to Alex Poole for spotting the issues with my edited output!)

ETA:

This could also be written as:

SELECT a.id a_id, b.id b_id, c.id c_id, d.id d_idFROM   a       LEFT OUTER JOIN (b                        LEFT OUTER JOIN c ON b.c_id = c.id AND c.conditions = 1                        LEFT OUTER JOIN d ON b.d_id = d.id AND d.conditions = 1)         ON a.id = b.a_id AND COALESCE(c.id, d.id) IS NOT NULLORDER BY a.id, b.id, b.c_id, b.d_id;

which is simpler but potentially harder to decipher the intent (and therefore harder to maintain in the future). I've added it here as I had no idea this was valid syntax, and you may feel it works better for you.

I'm adding another answer, because I really did remove the previous one as being incorrect. I think this is the correct logic:

SELECT A.ID, B.ID, C.ID, D.ID FROM A LEFT JOIN     (B LEFT JOIN      C      ON B.C_ID = C.ID AND C.CONDITIONS = 1 LEFT JOIN      D      ON B.D_ID = D.ID AND D.CONDITIONS = 1     )     ON B.A_ID = A.ID AND        (C.ID IS NOT NULL OR D.ID IS NOT NULL);

This does return the correct results when I test it.

It is an interesting problem. The idea is to use parentheses to "delay" the comparison between A and B. This allows the condition to also determine if there is a match on C or D.

Actually I found another way to do it with a subquery in the ON clause:

SELECT A.ID, B.ID, C.ID, D.ID FROM A LEFT JOIN B ON B.A_ID = A.ID     AND (B.C_ID IS NULL OR B.ID IN (SELECT B.ID FROM B JOIN C ON C.ID = B.C_ID AND C.CONDITIONS = 1)    AND (B.D_ID IS NULL OR B.ID IN (SELECT B.ID FROM B JOIN D ON D.ID = B.D_ID AND D.CONDITIONS = 1)LEFT JOIN C ON B.C_ID = C.ID LEFT JOIN D ON B.D_ID = D.ID;

I don't know whiwh solution will perform better having an other clause on the A table and big B, C and D tables.