Attach tables to date range in hive

Question

I need to connect to tableA to tableB on employee_id, and the cal_date from table A should be between the start date and the end date from table B. I ran below the query and received the error message below. Could you help me fix and request, Thank you for your help!

Both left and right aliases found in JOIN 'date_start' .

select a.*, b.skill_group from tableA a left join tableB b on a.employee_id= b.employee_id and a.cal_date >= b.date_start and a.cal_date <= b.date_end 

Answer 1

RTFM - Quoting LanguageManual Joins

The bush does not support join conditions that are not equal conditions since it is very difficult to express conditions such as display / reduction work.

You can try moving the BETWEEN filter to the WHERE clause, which results in a lousy partially Cartesian join, followed by cleanup after processing. Ugh. Depending on the actual power of your “skill group” table, it can run fast - or take whole days.