In Hive, I need one query to compare One Master table with three different lookup tables. If the record is matched with all 3 lookup tables, record should be updated as "Passed" If any one of the record is failed for any mis-match any of the tables, record should be updated and marked with mismatch value should be displayed
Master Table:
EMPNO EMPNAME CLASS SCHOOL LOCATION M1 M2 M3 101 SCOTT 4 MVM IDAHO 50 60 80 102 TIGER 7 MIV TEXAS 50 70 80 103 RAYON 3 MOV LONDON 80 75 80
EMPLOYEE:
EMPNO EMPNAME
101 SCOTT
102 TIGER
103 SPANGLER
104 MIKE
105 ALIGARGH
Address:
Class School Location PhoneNumber
4 MVM IDAHO 120232
6 TEM TEXAS 120394
3 MOV EDINBURGH 120479
6 PRAM VATICAN 12098
7 LEXI SALEM 12092
7 COLORS SALEM 12092
9 RAY SHIMLA 13490
Marks:
M1 M2 M3
50 60 80
50 60 80
80 75 80
90 90 90
30 50 45
Expected Result:
empno | empname | class | school | marks1 | marks2 | marks3 | employee | address | marks |
+--------+----------+--------+---------+---------+---------+---------+-----------+----------+--------+
| 101 | SCOTT | 3 | MOV | 50 | 70 | 80 | 1 | 1 | 1 | Matched with all 3 Records
| 102 | TIGER | 6 | MVM | 60 | 70 | 80 | 1 | 0 | 0 |Failed with Address and Marks Table, Expected Value LEXI and TEXAS in Address Table and 60 for M2 in Marks Table
| 103 | RAYON | 7 | COLORS | 90 | 90 | 90 | 0 | 0 | 1 | Failed with Employee and Address Table, Expected Value RAYON in Employee Table and LONDON in Address Table
What has been done so far,
Based on this query below, I could bring the print out 0 for Mis-match and 1 for Match for each of the record in Master Table:
select t.* ,case when e.EMPNO is null and e.EMPNAME is null then 0 else 1 end as EMPLOYEE ,case when a.Class is null and e.school is null then 0 else 1 end as Address ,case when m.Marks1 is null and m.Marks2 is null and m.Marks3 is null then 0 else 1end as Marks
from Master t left join EMPLOYEE e on e.EMPNO =t.EMPNO and e.EMPNAME = t.EMPNAME left join Address a on a.Class = t.Class and a.School = t.School left join Marks m on m.M1 = t.Marks1 and m.M2 = t.Marks2 and m.M3 = t.Marks3
empno | empname | class | school | marks1 | marks2 | marks3 | employee | address | marks | +--------+----------+--------+---------+---------+---------+---------+-----------+----------+--------+ | 101 | Scott | 3 | MOV | 50 | 70 | 80 | 1 | 1 | 1 | | 102 | Tiger | 6 | MVM | 60 | 70 | 80 | 1 | 0 | 0 | | 103 | Rayon | 7 | COLORS | 90 | 90 | 90 | 0 | 0 | 1 | +--------+----------+--------+---------+---------+---------+---------+-----------+----------+--------+
