CODEWITHSUNDEEP
hivehiveql
RAJ PATEL
RAJ PATEL

Reputation: 594

Find column from multiple column

I have one question about database query. Please refer below table.

Table : 1
ID   Country
1    x
2    y
3    z
4    k


Table : 2
eng  fre  fre1   fre2
x    x
x1   k      y   t
x2          n     z

Output Table
id  country
1   x
2   x1
3   x2
4   x1

How to achieve this in Hive?

Thank you so much for help.

Upvotes: 1

Views: 61

Answers (1)

leftjoin
leftjoin

Reputation: 38290

You can join three times but it may run slow:

select a.id, coalesce(b.eng, c.eng, d.eng)  as Country
  from table_1 a
       left join table_2 b on a.country=b.fre
       left join table_2 c on a.country=c.fre1
       left join table_2 d on a.country=d.fre2
;

Upvotes: 1

Related Questions