You can do a CROSS JOIN, then filter with the original inequality join condition. This would generate a lot of redundant tuples and may not work if you have large amounts of data.
On Thu, Aug 16, 2012 at 2:07 PM, Himanish Kushary <himan...@gmail.com>wrote: > Hi, > > We have two tables in the following structure : > > Table1 : > > | id | packcreatetime | packid | > ---------------------------------------------------------------------- > | 505 | 2012-07-16 11:51:12 | 111024 | > | 505 | 2012-07-18 11:52:13 | 111025 | > | 505 | 2012-07-19 11:53:14 | 111026 | > | 504 | 2012-07-17 23:50:13 | 101020 | > ------------------------------------------------------------------------ > > Table-2 > > | id | requesttime > ---------------------------------------- > | 505 | 2012-07-18 12:09:47 > | 505 | 2012-07-19 12:09:59 > | 505 | 2012-07-19 12:09:56 > | 505 | 2012-07-17 12:06:40 > | 505 | 2012-07-17 12:06:40 > | 505 | 2012-07-17 12:09:15 > | 504 | 2012-07-18 00:03:18 > | 504 | 2012-07-18 00:15:41 > > We want to find out the packid from Table1 where the is corresponding > in Table2 and the requesttime(in Table2) is between the > packcreatetime of two relevant records(in Table1) > > So for the above example the final output will be: > > | id | requesttime | packid > ------------------------------------------------------- > | 505 | 2012-07-18 12:09:47 | 111025 > | 505 | 2012-07-19 12:09:59 | 111026 > | 505 | 2012-07-19 12:09:56 | 111026 > | 505 | 2012-07-17 12:06:40 | 111024 > | 505 | 2012-07-17 12:06:40 | 111024 > | 505 | 2012-07-17 12:09:15 | 111024 > | 504 | 2012-07-18 00:03:18 | 101020 > | 504 | 2012-07-18 00:15:41 | 101020 > > > As we cannot use >= , <= in Hive joins the between logic cannot be > implemented in joins, is there any way to accomplish this or do we > need to write custom M/R code for this.Looking forward for any > suggestions to accomplish this. > > -- > Thanks & Regards > Himanish >
