hadoop - Inserting Data using join in Hive -

hi have 2 tables , after join want insert data third tables. problem facing have create multiple records based on value of join.

table 1     b ------- 1     x 2     y 3     x  table 2     c ------- 1     y 2     n 3     y 

i need join table 1 , table 2 on column , based on value of column c in table 2 need insert records in table 3 rule if column c value 'y' insert 3 records 'red','green','blue' if column c value 'n insert 2 records 'white','black'

so result should

table 3       d ----------- 1     red 1     green 1     blue 2     white 2     black 3     red 3     green 3     blue 

can let me know how achieve using hiveql ? thanks

you can create third table color

table color ------------------  flag color  y    red  y    blue  y    green  n    white  n    black 

now can join them easily

select * table1 t1 join table2 t2   on t1.a = t2.c join color c   on t2.c = c.flag