Joining multiple tables and getting MAX value in subquery PostgreSQL-CodePudding

I have 4 Tables in PostgreSQL with the following structure as you can see below:

  "Customers"
    ID   |     NAME 
    101        Max 
    102        Peter
    103        Alex


"orders"
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 

"orderEntry"
ID |   order_id |   product_id |  
1         3             10       
2         3             20       
3         3             30         
4         5             20       
5         5             40       
6         6             20        

"product"
ID |   min_duration
10         P10D                   
20         P20D        
30         P30D         
40         P40D       
50         P50D

Firstly I need to select "orders" with the max(created_at) date for each customer this is done with the query (it works!):

                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

the result will look like this:

customer_id |   order_id  |   CREATED_AT       
    101            3          2022-07-9             
    102            5          2022-06-18       
    103            6          2022-05-22

Secondly I need to select for each order_id from "orderEntry" Table, "products" with the max(min_duration) the result should be:

      order_id |   max(min_duration)  
         3             P30D       
         5             P40D       
         6             P20D

and then join results from 1) and 2) queries by "order_id" and the total result which I'm trying to get should look like this:

 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

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!

CodePudding user response：

You could make the first query to an CTE and use that to join the rest of the queries.

Like this.

WITH CTE AS (                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)
SELECT customerId,orderId,created_at,p.min_duration
FROM CTE 
    JOIN (SELECT "orderId", MAX("product_id") as product_id FROM "orderEntry" GROUP BY orderId) oe ON CTE.orderId = oe.orderId
    JOIN "product" pr ON oe.product_id  = pr."ID"