left outer joining three tables in oracle

Question

I have three tables like shown below:

And i am trying to get output like below:

Here is what i have tried till now

    SELECT table1.tb1_a,
CASE WHEN table3.tb3_a IS NOT NULL THEN
    tb3_b
    ELSE 'No city for him yet'
END AS 'City'
FROM table1
LEFT OUTER JOIN table2 ON
    table1.tb1_a = table2.tb2_a
LEFT OUTER JOIN table3 ON
    table2.tb2_a = table3.tb3_a
WHERE table3.tb3_a IN
    (

    )

And now i am struggling on how to select the maximum value of the tb3_a column

Answer 1

Something like this should work

select tb1_a, nvl(max(city), 'no city for him yet') thecity
from etc
group by tbl_a

Answer 2

This should do what you need:

SELECT t1.tb1_a, COALESCE(t3.tb3_a, 'No city for him yet') AS City
FROM table1 t1
LEFT JOIN (
    SELECT MAX(tb2_b) AS tb2_b, tb2_a
    FROM table2
    GROUP BY tb2_a
) t2 ON (t2.tb2_a = t1.tb1_a)
LEFT JOIN table3 t3 ON (t3.tb3_a = t3.tb2_b);

The key point is the in-line view in the middle where we create a kind of virtual table that contains the maximum tb2_b value for each tb2_a. We can then join to this to achieve the desired result.