Left join on a table with condition on others table

Question

I'm trying to left join multiple tables and having a clause on the third table when joining the second. I have tried with where clauses but it applies to the whole result when i just want to nullify the columns from the second table.

Let put an example it will be clearer. I have 4 tables :

CREATE TABLE A (ID INTEGER PRIMARY KEY);
CREATE TABLE B (ID INTEGER PRIMARY KEY, A_ID INTEGER, C_ID INTEGER, D_ID INTEGER);
CREATE TABLE C (ID INTEGER PRIMARY KEY, CONDITIONS INTEGER);
CREATE TABLE D (ID INTEGER PRIMARY KEY, CONDITIONS INTEGER);

The table B joining table A to tables C and D.

A sample data will be:

INSERT INTO A VALUES (1);
INSERT INTO A VALUES (2);
INSERT INTO A VALUES (3);

INSERT INTO C VALUES (1, 1);
INSERT INTO C VALUES (2, 1);
INSERT INTO C VALUES (3, 0);

INSERT INTO D VALUES (1, 0);
INSERT INTO D VALUES (2, 0);

INSERT INTO B VALUES (1, 1, 1, NULL);
INSERT INTO B VALUES (2, 1, 2, NULL);
INSERT INTO B VALUES (3, 1, 3, NULL);
INSERT INTO B VALUES (4, 2, NULL, 1);
INSERT INTO B VALUES (5, 2, NULL, 2);

The direct left join:

SELECT A.ID, B.ID, C.ID, D.ID 
FROM A 
LEFT JOIN B ON B.A_ID = A.ID 
LEFT JOIN C ON B.C_ID = C.ID 
LEFT JOIN D ON B.D_ID = D.ID;

returns the data:

╔══════╦══════╦══════╦══════╗
║ A.id ║ B.id ║ C.id ║ D.id ║
╠══════╬══════╬══════╬══════╣
║   1  ║   1  ║   1  ║ null ║
║   1  ║   2  ║   2  ║ null ║
║   1  ║   3  ║   3  ║ null ║
║   2  ║   4  ║ null ║   1  ║
║   2  ║   5  ║ null ║   2  ║
║   3  ║ null ║ null ║ null ║
╚══════╩══════╩══════╩══════╝

What I'm trying to do is to filter the B table with the data from the C and D tables. If I simply add a where condition on the request:

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

It returns:

╔══════╦══════╦══════╦══════╗
║ A.id ║ B.id ║ C.id ║ D.id ║
╠══════╬══════╬══════╬══════╣
║   1  ║   1  ║   1  ║ null ║
║   1  ║   2  ║   2  ║ null ║
║   3  ║ null ║ null ║ null ║
╚══════╩══════╩══════╩══════╝

Which it's logic but not what I want. What I want is:

╔══════╦══════╦══════╦══════╗
║ A.id ║ B.id ║ C.id ║ D.id ║
╠══════╬══════╬══════╬══════╣
║   1  ║   1  ║   1  ║ null ║
║   1  ║   2  ║   2  ║ null ║
║   2  ║ null ║ null ║ null ║
║   3  ║ null ║ null ║ null ║
╚══════╩══════╩══════╩══════╝

Which keeps a row with the A.ID = 2 but finds no value in B with the matching condition from C and D.

I tried to put the conditions in the ON clause joining the C and D tables but it keep the data from B:

╔══════╦══════╦══════╦══════╗
║ A.id ║ B.id ║ C.id ║ D.id ║
╠══════╬══════╬══════╬══════╣
║   1  ║   1  ║   1  ║ null ║
║   1  ║   2  ║   2  ║ null ║
║   1  ║   3  ║ null ║ null ║
║   2  ║   4  ║ null ║ null ║
║   2  ║   5  ║ null ║ null ║
║   3  ║ null ║ null ║ null ║
╚══════╩══════╩══════╩══════╝

I am now out of ideas to do the trick.

Answer 1

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_id
FROM   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 NULL
ORDER 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_id
FROM   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 NULL
ORDER 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.