HIVE for each row from one table join all rows from another table

Vote count:

0

In table A I have dates from 2014-01-01 to 2014-12-31

 action_date
 2014-01-01
 2014-01-02
 2014-01-03
 ...
 2014-12-31

In table B I have some information like

id name action_date deletion_date
1  nik  2013-01-01  2014-02-03
2  tom  2014-06-02  2014-06-30
3  lola 2013-12-30  2014-01-01

I want to join row from B table to each A table row if activation_date<=action_date<=deletion_date

e.g.

 action_date id name action_date deletion_date
 2014-01-01  1  nik  2013-01-01  2014-02-03
 2014-01-01  3  lola 2013-12-30  2014-01-01
 2014-01-02  1  nik  2013-01-01  2014-02-03
 2014-01-03  1  nik  2013-01-01  2014-02-03
   [...]
 2014-02-03  1  nik  2013-01-01  2014-02-03

 2014-06-02  2  tom  2014-06-02  2014-06-30
 2014-06-03  2  tom  2014-06-02  2014-06-30
   [...]
 2014-06-03  2  tom  2014-06-02  2014-06-30

I tried to use next query in Hive and in PostgreSQL. In PostgreSQL it works fine, but in Hive returns semanticexception [error 10017]: line 16:3 both left and right aliases encountered in join 'action_date'

   SELECT a.action_date, b.*
   FROM  b
   JOIN  a ON action_date>= activation_date AND action_date<=deletion_date

How to solve this problem in Hive?

asked 22 secs ago

Marta

394

HIVE for each row from one table join all rows from another table