Oracle-Join with same table multiple times with different where condition

Question

Here is my case,

SELECT 
A.TAB1_COL1,B.TAB2_COL4,C.TAB2_COL4 
FROM TABLE1 A, 
LEFT OUTER JOIN 
    (SELECT * FROM 
        (SELECT TAB2_COL1, TAB2_COL2, TAB2_COL4, ROW_NUMBER() OVER (PARTITION BY TAB2_COL1,TAB2_COL2 ORDER BY TAB2_COL3 DESC ) AS ROW_NUM 
            FROM TABLE2 
            WHERE TAB2_COL2=2
        ) WHERE ROW_NUM=1
    ) B ON A.TAB1_COL1=B.TAB2_COL1 
LEFT OUTER JOIN 
    (SELECT * FROM 
        (SELECT TAB2_COL1, TAB2_COL2, TAB2_COL4, ROW_NUMBER() OVER (PARTITION BY TAB2_COL1,TAB2_COL2 ORDER BY TAB2_COL3 DESC ) AS ROW_NUM 
            FROM TABLE2 WHERE TAB2_COL2=5
        ) WHERE ROW_NUM=1
    ) C ON A.TAB1_COL1=C.TAB2_COL1 AND A.TAB1_COL2=C.TAB2.COL5 
LEFT OUTER JOIN 
    (SELECT * FROM 
        (SELECT TAB2_COL1, TAB2_COL2, TAB2_COL4, ROW_NUMBER() OVER (PARTITION BY TAB2_COL1,TAB2_COL2 ORDER BY TAB2_COL3 DESC ) AS ROW_NUM 
            FROM TABLE2 WHERE TAB2_COL2=8
        ) WHERE ROW_NUM=1
    ) D ON A.TAB1_COL1=D.TAB2_COL1

This code will work.But, I'm left joining with same table multiple times. In my case, it was around 25 times. Reference table has around 200 million records. Partition to remove dups is taking much time.

Any other effective way of writing to make it process faster. Kindly help.

Thanks

You've left out the initial select line from the query. What is it that you're selecting to get the value columns? — Boneist
– Boneist, Commented Nov 1, 2018 at 9:27

Gordon Linoff · Accepted Answer · 2018-11-01 11:15:32Z

3

If I understand correctly, you can use conditional aggregation:

select t1.tab1_col1,
       max(case when tab2_col2 = 2 then tab2_col4 end),
       max(case when tab2_col2 = 5 then tab2_col4 end),
       max(case when tab2_col2 = 8 then tab2_col4 end)
from table1 t1 left join
     (select t2.*,
             row_number() over (partition by tab2_col1, tab2_col2 order by tab2_col3 desc) as seqnum
      from table2 t2
     ) t2
     on t1.tab1_col1 = t2.tab2_col1
group by t1.tab1_col1;

answered Nov 1, 2018 at 11:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle-Join with same table multiple times with different where condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related