Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive >> mail # user >> Need help in joining 2 tables


Copy link to this message
-
Need help in joining 2 tables
Hi,

Need to create a view in HIVE as per the below query (written in Oracle).
Need help to provide the equivalent query in HIVE to create view.
*EMP_ADDRESS*

Emp_Id
Address1
Address2
Address3
Address4
City
State
Pin
Country
Alternate_Address1
Alternate_Address2
Alternate_Address3
Alternate_Address4
Alternate_City
Alternate_State
Alternate_Pin
Alternate_Country
row_create_date
*EMP_MASTER*

Emp_Id
Name
DOB
Department
Sex
FathersName
row_create_date
*View Query*

select
MAST.Emp_Id,
MAST.Name,
MAST.DOB,
ADDR.Address1,
ADDR.Address2,
ADDR.Address3,
ADDR.Address4,
ADDR.City,
ADDR.State,
ADDR.Pin,
ADDR.Country
from EMP_MASTER MAST, EMP_ADDRESS ADDR
where
MAST.row_create_date = (select max(row_create_date) from EMP_MASTER where
Emp_Id = MAST.Emp_Id)
and ADDR.row_create_date = (select max(row_create_date) from EMP_ADDRESS
where Emp_Id = ADDR.Emp_Id)
regards,
Rams