Error in Hive Query while joining tables

Question

I am unable to pass the equality check using the below HIVE query.

I have 3 table and i want to join these table. I trying as below, but get error :

FAILED: Error in semantic analysis: Line 3:40 Both left and right aliases encountered in JOIN 'visit_date'

select t1.*, t99.* from table1 t1 JOIN 
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 ON
    ( v3.AS_upc= t3.upc_no AND v3.start_dt <= t3.visit_date  AND v3.end_dt >= t3.visit_date AND v3.adv_price <= t3.comp_price ) ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

EDITED based on help from FuzzyTree:

1st:

We tried to edit above query using between and where clause, but not getting any output from the query.

But If we changed the above query by removing the between clause with date, then I got some output based on "v3.adv_price <= t3.comp_price", but not using "date filter".

select t1.*, t99.* from table1 t1 JOIN
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
        where v3.adv_price <= t3.comp_price
    ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

2nd :

Next we tried to pass only one date as :

select t1.*, t99.* from table1 t1 JOIN
        (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
            where v3.adv_price <= t3.comp_price and v3.start_dt <= t3.visit_date
        ) t99 ON
        (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

So, now it's showing some result but if we pass both the start and end date filter, it; not showing any result.

Standby

Answer 1

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

Only equality joins, outer joins, and left semi joins are supported in Hive. Hive does not support join conditions that are not equality conditions as it is very difficult to express such conditions as a map/reduce job.

Try moving your inequalities to the where clause

select t1.*, t99.* from table1 t1 JOIN
    (select v3.*, t3.* from table2 v3 JOIN table3 t3 on (v3.AS_upc= t3.upc_no) 
        where t3.visit_date between v3.start_dt and v3.end_dt
        and v3.adv_price <= t3.comp_price
    ) t99 ON
    (t1.comp_store_id = t99.cpnumber AND t1.AS_store_nbr = t99.store_no);

Nucleotidase

I edited the query based on your help, but still unable to get the output from the query. I have updated my original post. – Standby

I've encountered the same issue. The solution suggested is good. I note however that the error message could be improved. It is misleading. – Miriam