title: Compare two tables using SQL JOIN
body: <p>This is <code>Table1</code> data.</p>
  
  <pre><code>USER_ID     |    PRODUCT_ID    |   TIMESTAMPS
  ------------+------------------+-------------
  1015826235       220003038067     *1004941621*
  1015826235       300003861266      1005268799
  1015826235       140002997245      1061569397
  1015826235      *210002448035*     1005542471
  </code></pre>
  
  <p>If you compare the  <code>Table1</code> data with the below <code>Table2</code> data, then the <code>PRODUCT_ID</code> in the last line of <code>Table1</code> data is not matching with the <code>ITEM_ID</code> in the last line in the below <code>Table2</code> data and also same with <code>TIMESTAMPS</code> in the first line of <code>Table1</code> data is not matching with <code>CREATED_TIME</code> in the first line of <code>Table2</code> data.</p>
  
  <pre><code>BUYER_ID     |    ITEM_ID         |   CREATED_TIME 
  -------------+--------------------+------------------------
  1015826235       220003038067       *2001-11-03 19:40:21*
  1015826235       300003861266        2001-11-08 18:19:59
  1015826235       140002997245        2003-08-22 09:23:17
  1015826235      *200002448035*       2001-11-11 22:21:11
  </code></pre>
  
  <p>So I need to show the result like this for the above example after JOINING Table1 with Table2- Either of the below output is fine for me.</p>
  
  <pre><code>BUYER_ID    |     ITEM_ID       |    CREATED_TIME         |    PRODUCT_ID     |       TIMESTAMPS
  ------------+-------------------+-------------------------+-------------------+-----------------
  1015826235       220003038067       *2001-11-03 19:40:21*      220003038067       *1004941621*
  1015826235      *200002448035*       2001-11-11 22:21:11      *210002448035*       1005542471
  </code></pre>
  
  <p><strong>OR</strong></p>
  
  <pre><code>BUYER_ID   |     ITEM_ID       |    CREATED_TIME         |     USER_ID      |       PRODUCT_ID     |   TIMESTAMPS
  -----------+-------------------+-------------------------+------------------+----------------------+------------------
  1015826235       220003038067       *2001-11-03 19:40:21*      1015826235           220003038067       *1004941621*
  1015826235      *200002448035*       2001-11-11 22:21:11       1015826235          *210002448035*       1005542471
  </code></pre>
  
  <p>Any help will be appreciated.</p>
  
  <p><strong>UPDATE:-</strong></p>
  
  <pre><code>select * from (select * from (select user_id, prod_and_ts.product_id as 
  product_id, prod_and_ts.timestamps as timestamps from testingtable2 LATERAL VIEW
  explode(purchased_item) exploded_table as prod_and_ts) prod_and_ts LEFT OUTER 
  JOIN table2 ON ( prod_and_ts.user_id = table2.buyer_id AND table2.item_id =
  prod_and_ts.product_id AND prod_and_ts.timestamps = UNIX_TIMESTAMP
  (table2.created_time) ) where table2.buyer_id IS NULL) set_a LEFT OUTER JOIN
  table2 ON (set_a.user_id = table2.buyer_id AND  ( set_a.product_id = 
  table2.item_id OR set_a.timestamps = UNIX_TIMESTAMP(table2.created_time) ));
  </code></pre>