Pull conditional statement results based on multiple table joins

Question

I have 3 tables to join to get the output in the below format.

My table 1 is like:

--------------------------------------------------------
    T1_ID1      |       T1_ID2      |       NAME
--------------------------------------------------------
    123         |       T11231      |       TestName11
    123         |       T11232      |       TestName12
    234         |       T1234       |       TestName13
    345         |       T1345       |       TestName14
--------------------------------------------------------

My table 2 is like:

--------------------------------------------------------
    T2_ID1      |       T2_ID2      |       NAME
--------------------------------------------------------
    T11231      |       T21231      |       TestName21
    T11232      |       T21232      |       TestName21
    T1234       |       T2234       |       TestName22
--------------------------------------------------------

My table 3 is like:

----------------------------------------------------------
    T3_ID1      |       TYPE        |       REF
----------------------------------------------------------
    T21231      |       1           |       123456
    T21232      |       2           |       1234@test.com
    T2234       |       2           |       123@test.com
----------------------------------------------------------

My desired output is:

------------------------------------------------------
    T1_ID1      |       PHONE       |       EMAIL
------------------------------------------------------
    123         |       123456      |   1234@test.com
    234         |                   |   123@test.com
    345         |                   |
------------------------------------------------------

Requirements:

• T1_ID2 of table 1 left joins with T2_ID1 of table 2.
• T2_ID2 of table 2 left joins with T3_ID1 of table 3.
• TYPE of table 3 specifies 1 if the value is phone and specified 2 if value is email.
• My output should contain T1_ID1 of table 1 and its corresponding value of REF in table 3, with the REF in the same row. That is, in this case, T1_ID1 with value 123 has both phone and email. So, it is displayed in the same row in output.
• If phone alone is available for corresponding value of T1_ID1, then phone should be populated in the result with email as null and vice versa.
• If neither phone nor email is available, nothing should be populated.

I had tried the below SQLs but in vain. Where am I missing? Please extend your help.

Option 1:

SELECT DISTINCT
    t1.t1_id1,
    t3.ref
    || (
        CASE
        WHEN t3.type = 1 THEN
        1
        ELSE
        0
        END
    ) phone,
    t3.ref
    || (
        CASE
        WHEN t3.type = 2 THEN
        1
        ELSE
        0
        END
    ) email
FROM
    table1 t1
    LEFT JOIN table2    t2 ON t1.t1_id2 = t2.t2_id1
    LEFT JOIN table3    t3 ON t2.t2_id2 = t3.t3_id1;

Option 2:

SELECT DISTINCT
    t1.t1_id1,
    t3.ref, 
    (
        CASE
        WHEN t3.type = 1 THEN
        1
        ELSE
        0
        END
    ) phone,
    t3.ref,
    (
        CASE
        WHEN t3.type = 2 THEN
        1
        ELSE
        0
        END
    ) email
FROM
    table1 t1
    LEFT JOIN table2    t2 ON t1.t1_id2 = t2.t2_id1
    LEFT JOIN table3    t3 ON t2.t2_id2 = t3.t3_id1;

Option 3:

SELECT DISTINCT
    t1.t1_id1,
    (
        CASE
        WHEN t3.type = 1 THEN
        1
        ELSE
        0
        END
    ) phone,
    (
        CASE
        WHEN t3.type = 2 THEN
        1
        ELSE
        0
        END
    ) email
FROM
    table1 t1
    LEFT JOIN table2    t2 ON t1.t1_id2 = t2.t2_id1
    LEFT JOIN table3    t3 ON t2.t2_id2 = t3.t3_id1;

Answer 1

select t1_id1, max(t3.ref )phone, max(t33.ref) email
 from table1
 left outer join
 table2 on t1_id2=t2_id1
 left outer join table3 t3 on t3.t3_id1=t2_id2 and t3.type=1
 left outer join table3 t33 on t33.t3_id1=t2_id2 and t33.type=2
 group by t1_id1

if you have maximum one phone and one email in table3 for each t2_id2 entry in table2.