CODEWITHSUNDEEP
hivehiveqlhadoop2
Babu
Babu

Reputation: 169

Inner Join not giving results in Hive

I am trying to join 4 talbles. One is the main table and joinging 3 other tables with this main table. Please see data looks like as follows:

enter image description here

enter image description here

Expected output.

enter image description here

Here is the query I developed and its always returning 0 rows.

select COALESCE(TableB.Date, TableC.Date, TableD.Date), 
       COALESCE(count(key1),0), 
       COALESCE(count(key2),0), 
       COALESCE(count(key3),0)
FROM TableA A JOIN TableB B on A.Date = B.Date 
JOIN TableC C on A.Date = C.Date
JOIN TableD D on A.Date = D.Date
Group by COALESCE(TableB.Date, TableC.Date, TableD.Date);

when I ran individual query on each table(see below query) with TableA, it returns the data but when I am joining with all 3 tables, its not retuning any data.

select TableB.Date, count(key1)
  FROM TableA A JOIN TableB B on A.Date = B.Date 
Group by TableB.Date;

I am not sure what's going wrong and could some one help to understand where is the issue in join query.

Thanks, Babu

Upvotes: 0

Views: 729

Answers (3)

sandeep rawat
sandeep rawat

Reputation: 4957

I think you are looking for full outer join . Here will be sample code

select COALESCE(a.Date, b.Date, c.Date,d.Date), 
       COALESCE(sum(key1),0), 
       COALESCE(sum(key2),0), 
       COALESCE(sum(key3),0)

(select distinct date as date from tableA ) as a
full outer join 
(select  date ,sum(key1) as key1 from tableB group by date) as b
on a.date=b.date
full outer join
(select  date ,sum(key2) as key2 from tableB group by date) as c
on a.date=c.date
full outer join
(select  date ,sum(key3) as key2 from tableB group by date) as d
on a.date=d.date
Group by COALESCE(a.Date, b.Date, c.Date,d.Date);`

Upvotes: 0

serge_k
serge_k

Reputation: 1772

Do the joins in the subquery, then do grouping, e.g.,

with join_table as (select COALESCE(TableB.Date, TableC.Date, TableD.Date) as Date, 
    key1, key2, key3
    FROM TableA A JOIN TableB B on A.Date = B.Date 
    JOIN TableC C on A.Date = C.Date
    JOIN TableD D on A.Date = D.Date
)
select Date,
       COALESCE(count(key1),0), 
       COALESCE(count(key2),0), 
       COALESCE(count(key3),0)
  from join_table
 group by Date

In fact, you don't need to coalesce the join keys in the inner join.

Upvotes: 1

Koushik Roy
Koushik Roy

Reputation: 7387

You can try this -

select A.Date, 
       COALESCE(count(key1),0), 
       COALESCE(count(key2),0), 
       COALESCE(count(key3),0)
FROM TableA A 
LEFT JOIN TableB B on A.Date = B.Date 
LEFT JOIN TableC C on A.Date = C.Date
LEFT JOIN TableD D on A.Date = D.Date
Group by A.Date;

I used left join instead of inner join because we need all data from Table A. Also first column should come from table A which is driver table.

Upvotes: 0

Related Questions