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

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?

CodePudding user response：

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.