Need SQL outer join help with 3 tables

Question

See below example. ID_1 and ID_A are primary keys for table_1 and table_3 respectively. Table_2, the primary key is ID_1 and ID_A. ID_A is joined to one and only one ID_1 via table_2 due to business rules.

I need a query that will return all rows from table_1 where the value_1 = 11 and joined to that, I need only the rows from table_3 that have a value_a = 'a' if it joins to a row in table_1 that has a value_1 = 11.

Please see results required. Note there is no row

|1 | a |

in the result set because value_a for id_a = 'b';

I've tried a left outer join but all ID_1 are returned. I've also tried CTE, to no avail. Here's one SQL, I tried.

Select ID_1, Value_1, ID_A, VALUE_A
 From TABLE_1 t1
 Join TABLE_2 t2 on t1.ID_1 = t2.ID_1
 Left Outer Join TABLE_3 t3 on t2.ID_A = t3.ID_A;

I've also tried using a temporary table which include only the results from TABLE_1 that I need, but the left outer join using the temp table still returns the above mentioned row.

The DB is Oracle, but I prefer a standard solution. Also, performance is a consideration, given that TABLE_1 has over 200 million rows and TABLE_3 has about 4 times that many.

TABLE_1

| ID_1 | Value_1 |
-------------------
| 1 | 11 |
| 2 | 12 |
| 3 | 11 |
| 4 | 13 |

TABLE_2

| ID_1 | ID_A |
----------------
| 1 | a |
| 1 | b |
| 3 | c |
| 4 | d |

TABLE_3

| ID_A | Value_A |
------------------
| a | b |
| b | a |
| c | e |
| d | a |

Results Required

| ID_1 | ID_A | Value_1 | Value_A |
----------------------------------------
| 1 | b | 11 | a |
| 3 | <NULL> | 11 | <NULL> |

Answer 1

First, join TABLE_2 and TABLE_3 using an inner join and additionally filtering on Value_A = 'a':

SELECT
 t2.ID_1,
 t3.ID_A,
 t3.Value_A
FROM
 TABLE_2 t2
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
WHERE
 t3.Value_A = 'a'

This will give you the following result set:

ID_1 ID_A Value_A
---- ---- -------
1 b a
4 d a

Now use the above as a derived table and join it, using an outer join this time, to TABLE_1, additionally filtering the results on Value_1 = 11:

SELECT
 t1 .ID_1,
 t23.ID_A,
 t1 .Value_1,
 t23.Value_A
FROM
 TABLE_1 t1
 LEFT JOIN
 (
 SELECT
 t2.ID_1,
 t3.ID_A,
 t3.Value_A
 FROM
 TABLE_2 t2
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A
 WHERE
 t3.Value_A = 'a'
 ) t23 ON t1.ID_1 = t23.ID_1
WHERE
 t1.Value_1 = 11
;

That will give you the output you want:

ID_1 ID_A Value_1 Value_A
---- ---- ------- -------
1 b 11 a
3 NULL 11 NULL

However, nesting a query is not the only way to solve your problem – you can also use a nested join, which is much more concise:

SELECT
 t1.ID_1,
 t3.ID_A,
 t1.Value_1,
 t3.Value_A
FROM
 TABLE_1 t1
 LEFT JOIN
 TABLE_2 t2
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
 ON t1.ID_1 = t2.ID_1
WHERE
 t1.Value_1 = 11
;

The last query implements exactly the same logic as the previous query: first, tables TABLE_2 and TABLE_3 are joined and the result is filtered, then it is joined to TABLE_1 and the final set is filtered again. Some people also add brackets around a nested join:

FROM
 TABLE_1 t1
 LEFT JOIN
 (
 TABLE_2 t2
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
 )
 ON t1.ID_1 = t2.ID_1

to make it clearer (perhaps both for themselves and for future maintainers) that the nested join takes place before the outer-level one, logically, although the syntax is unambiguous enough without them.

Nevertheless, many people find it confusing even with brackets, and if you find yourself struggling with it as well, there is another option – right outer join:

SELECT
 t1.ID_1,
 t3.ID_A,
 t1.Value_1,
 t3.Value_A
FROM
 TABLE_2 t2
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
 RIGHT JOIN TABLE_1 t1 ON t1.ID_1 = t2.ID_1
WHERE
 t1.Value_1 = 11
;

Again, the logical sequence of events specified by the FROM clause repeats that of the derived table solution: a join between TABLE_2 and TABLE_3, filtered, is followed by a join with TABLE_1. The different syntax does not alter the outcome and the results produced still match your requirements.

Answer 2

You've obfuscated your question and schema so much that it is very hard for me to understand what you want. Here is a query that should give you the output you want given your sample data or close to it.

SELECT
 t1.ID_1
 , MAX(CASE WHEN t3.Value_A IS NOT NULL THEN t3.ID_A ELSE NULL END) AS ID_A
 , t1.Value_1
 , MAX(CASE WHEN t3.Value_A IS NOT NULL THEN t3.Value_A ELSE NULL END) AS Value_A
FROM TABLE_1 t1
 LEFT JOIN TABLE_2 t2 on t1.ID_1 = t2.ID_1
 LEFT JOIN TABLE_3 t3 on t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
WHERE
 t1.Value_1 = 11
GROUP BY
 t1.ID_1
 , t1.Value_1;

That is one way you could write it in T-SQL, and I think it will work in Oracle too.

Answer 3

Your SQL is not referencing Alias correctly. If you're doing a JOIN you should reference the source table in your query for OUR sake.

SELECT ID_1, Value_1, ID_A, VALUE_A
 FROM TABLE_1 t1
 INNER JOIN TABLE_2 t2 ON t1.ID_1 = t2.ID_1
 INNER JOIN TABLE_3 t3 ON t2.ID_A = t3.ID_A AND t3.Value_A = 'a'
 WHERE t1.Value_1 = 11 ;

• Erik: I don't think this will return the result desired when there is not a match with t3.Value_A = 'a'