Client list with last order

January 5, 2022

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

>Solution :

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;