Hive: filter table by date of another table with matching key

user_11715698 0
Viewed 41

I need to find pre-transplant height measurements.

Height

Pnt | Height | Date
001 |  10    | 'yyyy-mm-dd'
001 |  11    | 'yyyy-mm-dd'
002 |  15    | 'yyyy-mm-dd'
002 |  15    | 'yyyy-mm-dd'
003 |  08    | 'yyyy-mm-dd'
003 |  07    | 'yyyy-mm-dd'

Liver

Pnt |  Desc  |     Date
001 |  Liver | 'yyyy-mm-dd'
002 |  Liver | 'yyyy-mm-dd'
003 |  Liver | 'yyyy-mm-dd'

How can I select entire rows in table Height with height measurements taken before the transplant procedure date in Liver with respect to the Pnt key?

hive hadoop
1 Answers

can you not join both tables on pnt and date comparison?

select h.* from height h 
inner join liver l on l.pnt=h.pnt and h.date < l.date

h.date < l.date - this should give you data for height measurements taken before the transplant procedure date.

user_64305230
Related