How to avoid correlating subqueries inside CASE WHEN?

Question

Suppose we have these four tables:

create table table_x(x number, title varchar2(100));

create table table_y(y number, x number, title varchar2(100));

create table table_z(z number, y number, x number, title varchar2(100));

create table table_main(z number, y number, x number);

It's required to fetch title for each row in table_main according the rule:

select 
    case
         when z is not null
         then (select title from table_z where x = tm.x and y = tm.y and z = tm.z)

         when y is not null
         then (select title from table_y where x = tm.x and y = tm.y)

         when x is not null
         then (select title from table_x where x = tm.x)
     end as title
 from table_main tm;

I'm looking for a more readable solution producing equivalent results.

Is there a way to refactor the query replacing case when statement with table joins or somehow else?

Answer 1

my approach would be:

SELECT COALESCE(tz.title, ty.title, tx.title) AS mytitle
  FROM table_main tm
  LEFT OUTER JOIN table_x tx
    ON tx.x_number = tm.x_number
  LEFT OUTER JOIN table_y ty
    ON ty.x_number = tm.x_number
   AND ty.y_number = tm.y_number
  LEFT OUTER JOIN table_z tz
    ON tz.x_number = tm.x_number
   AND tz.y_number = tm.y_number   
   AND tz.z_number = tm.z_number