title: Joining multiple tables and getting MAX value in subquery PostgreSQL
body: <p>I have 4 Tables in PostgreSQL with the following structure as you can see below:</p>
  <pre><code>  &quot;Customers&quot;
      ID   |     NAME 
      101        Max 
      102        Peter
      103        Alex
  
  
  &quot;orders&quot;
  ID | customer_id |   CREATED_AT  
  1         101         2022-05-12       
  2         101         2022-06-14       
  3         101         2022-07-9         
  4         102         2022-02-14       
  5         102         2022-06-18       
  6         103         2022-05-22 
  
  &quot;orderEntry&quot;
  ID |   order_id |   product_id |  
  1         3             10       
  2         3             20       
  3         3             30         
  4         5             20       
  5         5             40       
  6         6             20        
  
  &quot;product&quot;
  ID |   min_duration
  10         P10D                   
  20         P20D        
  30         P30D         
  40         P40D       
  50         P50D      
  </code></pre>
  <ol>
  <li>Firstly I need to select &quot;orders&quot; with the <code>max(created_at)</code> date for each customer this is done with the query (it works!):</li>
  </ol>
  <pre class="lang-sql prettyprint-override"><code>                SELECT  c.id as customerId,
                          o.id as orderId,
                          o.created_at
                  FROM    Customer c
                          INNER JOIN Orders o
                              ON c.id = o.customer_id
                          INNER JOIN
                                  (
                                      SELECT  customer_id, MAX(created_at) Max_Date
                                      FROM    Orders
                                      GROUP   BY customer_id
                                  ) res ON  o.customer_id = res.customer_id AND
                                          o.created_at = res.Max_date
  </code></pre>
  <p>the result will look like this:</p>
  <pre><code>customer_id |   order_id  |   CREATED_AT       
      101            3          2022-07-9             
      102            5          2022-06-18       
      103            6          2022-05-22 
  </code></pre>
  <ol start="2">
  <li>Secondly I need to select for each order_id from &quot;orderEntry&quot; Table, &quot;products&quot; with the <code>max(min_duration)</code> the result should be:</li>
  </ol>
  <pre><code>      order_id |   max(min_duration)  
           3             P30D       
           5             P40D       
           6             P20D  
  </code></pre>
  <p>and then join results from 1) and 2) queries by &quot;order_id&quot; and the total result which I'm trying to get should look like this:</p>
  <pre><code> customer_name |      customer_id |  Order_ID |   Order_CREATED_AT |      Max_Duration
         Max               101            3          2022-07-9                 P30D
         Peter             102            5          2022-06-18                P40D
         Alex              103            6          2022-05-22                P20D
  </code></pre>
  <p>I'm struggling to get query for 2) and then join everything with query from 1) to get the result. Any help I would appreciate!</p>