CODEWITHSUNDEEP
hivehiveql
Marco99
Marco99

Reputation: 1659

Hive join with distinct

I have two tables TableA and TableB.

TableA has columns REC_NUM and ITEM_ID. TableB has columns ITEM_ID, UNITS.

I need to take a distinct of ITEM_ID from TableA, and fetch all the records from TableB for every matching value of ITEM_ID from TableA with that of the ITEM_ID of TableB.

Can someone please let know I can do this.

Upvotes: 0

Views: 1726

Answers (2)

kirtan_shah
kirtan_shah

Reputation: 78

as per the question :

schema of TABLEA: REC_NUM INT,ITEM_ID INT

schema of TABLEB: ITEM_ID INT,UNITS INT

Following query should work:

SELECT b.* FROM (SELECT DISTINCT ITEM_ID FROM TABLEA) a JOIN TABLEB b ON a.ITEM_ID=b.ITEM_ID;

Upvotes: 1

hlagos
hlagos

Reputation: 7947

correct me if I am wrong

select ITEM_ID, UNITS from TableB where ITEM_ID in (select ITEM_ID from tableA)

I am not sure why you want to use distinct, should it be used with the column REC_NUM?

Upvotes: 1

Related Questions