Client list with last order-CodePudding

I have two tables, Cients and Orders and I'm try to create a list with client / last order date

CLIENTS
client_id | client_name
1         | Mark
2         | John
3         | Paul
4         | Sid

ORDERS
order_id | order_client_id | last_order_date
1        | 1               | 2022-01-01
2        | 2               | 2022-01-03
3        | 4               | 2022-01-04

If I'm using a LEFT JOIN results are partials, because Paul not have orders and this one not appear on list.

   SELECT  *
     FROM  clients
LEFT JOIN orders ON order_client_id = client_id
    WHERE client_role = ?


// CURRENT RESULTS
Mark   2022-01-01
John   2022-01-03
Sid    2022-01-04

I'm like to obtain a full client list and if someone not have an order, date remain blank

// EXPECTED RESULTS
Mark   2022-01-01
John   2022-01-03
Paul   
Sid    2022-01-04

CodePudding user response：

Better check it out on SQLFiddle.

Select c.client_name, last_order_date From clients c
Left Join (select order_client_id, max(last_order_date) as last_order_date 
from orders group by order_client_id) o on o.order_client_id = c.client_id;

CodePudding user response：

Assuming that you are using autoIncrement id's just use the GROUP BY the client_id and ORDER BY the last_order_date

 SELECT  *
         FROM  clients
    LEFT JOIN orders ON orders.order_client_id = clients.client_id
        GROUP BY clients.client_id 
        ORDER BY orders.last_order_date DESC

you don't need to use the WHERE if you are looking for all clients