How to search a string from a collection of string in hive column

Question

I have below two input table : table 1 and table 2

Input : table 1 id and currency are of type string

ID	Currency
1	USD,JPY,EUR
2	NOK,MXN
3	AUD
4	AUD,HKD

input: table2 exception_currency are of type string

exception_currency
AUD
NOK
USD
HKD

expected output as below Exception is yes if the each currency column values from table 1 and exception_currency from table 2 are not matching. For example for id 1, the exception is "YES" because JPY and EUR are not available in the table 2.

ID	Currency	Exception
1	USD,JPY,EUR	YES
2	NOK,MXN	YES
3	AUD	NO
4	AUD,HKD	NO

I tried below code but not getting expected results.

select
id,
currency,
case when array_contains(split(t1.currency,','), t2.exception_currency) then 'NO' else 'YES' as exception
from table1 t1 left join table2 t2 on (t1.currency=t2.exception_currency);

Answer 1

First seperate the comma separated values into rows and then compare it with table2.

Select id, currency,
Max(excep) as exception 
from table1
Join (Select
Id, Case when exception_currency is null then 'yes' else 'no' end excep
From 
(SELECT ID, sep_curr
FROM table1 lateral view explode(split(currency,',')) currency AS sep_curr) rs
Left join table2 on exception_currency= sep_curr
) Rs on rs.id= table1.id
Group by id, currency

Here lateral view will generate rows from comma separated values.

Joining it with exception table to get non existent exception.

Then finally max will show yes if at least one value is exception.