SQL query to fetch data based on column value

Is it possible to get entire data in one attempt for this scenario? I have this query where I need retrieve product name from another table.

   SELECT T1.CASE_ID, 
       T2.PRODUCT_ID,
       T2.LEVEL,
       (CASE WHEN T2.LEVEL = 3 THEN T3.PARENT_PRODUCT_ID
            WHEN T2.LEVEL = 2 THEN T2.PRODUCT_ID
            WHEN T2.LEVEL = 1 THEN NULL END) AS NEW_PRODUCT_ID,
        T3.PRODUCT_NAME
   FROM TABLE1 T1
   LEFT OUTER JOIN TABLE2 T2
        ON T1.CASE_ID = T2.CASE_ID
   LEFT OUTER JOIN TABLE3 T3
        ON T2.PRODUCT_ID=T3.PRODUCT_ID 

Right now the T3.PRODUCT_NAME return value based on T2.PRODUCT_ID but what I really need is to able to retrieve data based on the value from NEW_PRODUCT_ID?

Here is my expected output:

Hope this makes sense.

Answer

You can use a subquery

SELECT T4.CASE_ID, T4.PRODUCT_ID, T4.LEVEL, T4.NEW_PRODUCT_ID, T5.PRODUCT_NAME
FROM (
    SELECT T1.CASE_ID, 
       T2.PRODUCT_ID,
       T2.LEVEL,
       (CASE WHEN T2.LEVEL = 3 THEN T3.PARENT_PRODUCT_ID
            WHEN T2.LEVEL = 2 THEN T2.PRODUCT_ID
            WHEN T2.LEVEL = 1 THEN NULL END) AS NEW_PRODUCT_ID,
        T3.PRODUCT_NAME
   FROM TABLE1 T1
   LEFT OUTER JOIN TABLE2 T2
        ON T1.CASE_ID = T2.CASE_ID
   LEFT OUTER JOIN TABLE3 T3
        ON T2.PRODUCT_ID=T3.PRODUCT_ID 
) AS T4
LEFT OUTER JOIN TABLE3 T5
    ON T4.NEW_PRODUCT_ID=T5.PRODUCT_ID