Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB