hive - In SQL how do I match criteria across rows? -

this question has answer here:

• how return rows have same column values in mysql 2 answers

let's have data like:

id value  x  y  z b  x c  x c  y c  w 

and want find id have both x , y values or x , w. these stored in second table:

value1 value2 x      y x      w 

and return:

id value1 value2  x      y c  x      y c  x      w 

this needs work @ scale, first table has 100m lines , second table thousands of rows. run in impala or hive.

you can using group by, having , logic:

select id data d cross join      table2 t2 group id, t2.value1, t2.value2 having sum(case when d.value = t2.value1 1 else 0 end) > 0 ,        sum(case when d.value = t2.value2 1 else 0 end) > 0;