Search code examples
sqljoinhiveimpala

Impala: duplicate table alias when trying joining on multiple columns

I want to left outer join table A and table B on multiple columns. Below is my code:

select * from table_A

    left outer join table_B
     on (table_A.a1 = table_B.b1)

    left outer join table_B 
     on (table_A.a2 = table_B.b2)

But then I got error:

HiveServer2Error: AnalysisException: Duplicate table alias: 'table_B'

Does anyone know whatI did wrong here? Thanks!

Solution

  • Use different table aliases as you are joining the same table twice.

    select *  -- use column names here instead of *
from table_A ta
left outer join table_B tb1 on (ta.a1 = tb1.b1)
left outer join table_B tb2 on (ta.a2 = tb2.b2)