In Hive, Need to print mismatch value compared fro

Question

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 | +--------+----------+--------+---------+---------+---------+---------+-----------+----------+--------+