I need help in avoiding joining same table multiple times. Here is my sample query.
Table1 have 3 columns ABC_ID,DEF_ID,XYZ_ID and is associated to table2 on ID column . 3 column values in table1 can be null and If values are present then i need to return associated value from table 2 using different column names as in select statement.
As i am using left join i ended up joining table2 three times with table1 on each type if ID column which is having lot of performance issues. How can i write this in different way to avoid join multiple times. Here is sample data. Any help is appreciated
select
(
CASE
WHEN ( table2.ID = table1.ABC_ID)
THEN table2.ID_VAL
ELSE 'TEST1'
END ) AS "TEST1",
(
CASE
WHEN (table2a.ID = table1.DEF_ID)
THEN table2a.ID_VAL
ELSE 'TEST2'
END ) AS "TEST2",
(
CASE
WHEN (table2b.ID = table1.XYZ_ID)
THEN table2b.ID_VAL
ELSE 'TEST3'
END ) AS "TEST3"
from table1 table1
left join table2 table2 on (table2.ID=table1.ABC_ID)
left join table2 table2a on ( table2a.id=table1.DEF_ID)
left join table2 table2b on ( table2b.id=table1.XYZ_ID)
where table1.Id_NUM='1'
Table1
Id_NUM ABC_ID DEF_ID XYZ_ID
1 12345 456789 32145
2 null 456789 32145
3 12345 null null
Table2
ID ID_VAL
12345 abcded
456789 kjwsddk
321456 wedfgfv
OUTPUT
TEST1 TEST2 TEST3
12345 456789 32145