title: Optimizing query and collating results
body: <p>I have 2 tables as follows:</p>
  
  <pre><code>Table 1
  ID  |  Date1  |  Date 2
  ------------------------
  1   | 20180201| 20180201
  1   | 20190201| 20190120
  1   | 20200201| 20200129
  2   | 20200810| 20200731
  3   | 20191121| 20191023
  3   | 20201030| 20201024
  .
  .
  .
  
  Table 2
  ID  |  Tag    |  rel_ID
  ------------------------
  1   | related |  3
  1   | related |  8
  1   | related |  10
  2   | related |  4
  2   | related |  5
  .
  .
  .
  </code></pre>
  
  <p>The desired output is as follows:</p>
  
  <pre><code>ID|Date1|Date2|rel_ID_1|Date1|Date2|rel_ID_2|Date1|Date2|rel_ID_3|Date1|Date2|Prim ID|Date1|Date 2
  1|20200201|20200129|3|20201030|20201024|8|20201104|20201030|10|20200301|20200229|1|20200201|20200129
  </code></pre>
  
  <p>The Date1 and Date 2 fields need to be the max(date1) and max(date2) for both the ID and rel_ID. Once I get this, I will need to decide if the ID or rel_ID is a primary ID based on Date1 being the min value between all the related IDs. 
  I have tried multiple sub-queries to achieve this but they take a very long time. Table 2 has around a million rows and Table 1 has 800k rows.
  Is this is even possible with just a query? Or would this require a code including SQL's? </p>
  
  <p>A query that I've tried which runs for a very long time is:</p>
  
  <pre><code>select t2.ID,t1.date1,t1.date2,t2.rel_ID,rel_date1 = (select max(date1) from t1 where ID = rel_ID), rel_date2 = (select max(date2) from t1 where ID = rel_ID)
  from
  (select distinct ID,max(date1),max(date2) from t1 group by ID) as t1,
  (select distinct ID,rel_ID from t2) as t2
  where t1 .ID = t2.ID
  </code></pre>