CODEWITHSUNDEEP
hadoophqlhivehiveql
Raj
Raj

Reputation: 149

Hive: Joining tables with different scenarios

I have a question on joining tables in a different scenario. Please find the sample tables below.

Capacity of expected table row 3-5 should be repeated as table 2 does not have those fields.

could anyone please help to get expected table?

Table 1:

No ProjectID Capacity

1 514 4

2 418 10

3 418 30

4 401 40

5 502 41

Table2:

NO ProjectID Capacity1 Capacity2

1 514 4 10

2 418 10 20

Expected Table:

NO ProjectID Capacity1 Capacity2

1 514 4 10

2 418 10 20

3 418 30 30

4 401 40 40

5 502 41 41

Upvotes: 0

Views: 1330

Answers (1)

Balaswamy Vaddeman
Balaswamy Vaddeman

Reputation: 8540

1.Do left outer join

2.For the values not matching take them from table 1 with if condition.

   select t1.no,t1.projectid,t1.capacity1,if(t2.capacity2 is null,t1.capacity,t2.capacity)
    from table1 t1 left outer join table2 t2 on t1.no=t2.no

I think above query meets your requirement let me know if need any more help.

Upvotes: 1

Related Questions