Pilou Pilou -4 years ago 86
SQL Question

Left join on a table with condition on others table

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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download