MySQL - Join two tables by most recent date

Question

I want to join these two tables, showing the most recent due date for every client:

Table "clients":

| id_client |       name |
|-----------|------------|
|         1 | Customer 1 |
|         2 | Customer 2 |
|         3 | Customer 3 |

Table "invoices" (FK id_client):

| id_invoice | id_client |   due_date | payment_frequency |
|------------|-----------|------------|-------------------|
|          1 |         1 | 2018-11-30 |           monthly |
|          2 |         1 | 2018-12-30 |           monthly |
|          3 |         2 | 2019-01-01 |         quarterly |
|          4 |         2 | 2019-01-01 |         quarterly |

Desired result:

| id_client |       name |   due_date | payment_frequency |
|-----------|------------|------------|-------------------|
|         1 | Customer 1 | 2018-12-30 |           monthly |
|         2 | Customer 2 | 2019-01-01 |         quarterly |
|         3 | Customer 3 |     (null) |            (null) |

Details:

It should return all clients records, even those with no invoices (null).
Some customers have more than one invoice that due on the same date (id_invoice 3 and 4 in this example), but only one record should be returned.

I was able to make it work through the following query:

SELECT 
    c.id_client,c.name,
    (SELECT due_date FROM invoices WHERE id_client=c.id_client ORDER BY due_date DESC LIMIT 1) AS due_date,
    (SELECT payment_frequency FROM invoices WHERE id_client=c.id_client ORDER BY due_date DESC LIMIT 1) AS payment_frequency
FROM 
  clients AS c

I think there are more elegant and better-performing ways, through joins. Can you give me your suggestion please?

This table structure, data and query at Fiddle Ps. Despite the question is marked as a duplicate, other questions and answers do not solve this case.

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-01-03T11:47:55.077

-1

Please check this out

select 
  c.id_client,
  c.name,
  max(i.due_date) due_date,
  max(i.payment_frequency) payment_frequency
from clients c
left outer join invoices i
  on c.id_client = i.id_client
group by
  c.id_client,
  c.name

Edit: Please check my edited answer

select 
  c.id_client,
  c.name,
  i.due_date due_date,
  i.payment_frequency payment_frequency
from clients c
left outer join invoices i
  on c.id_client = i.id_client
where due_date is null or due_date = (select max(due_date) from invoices where id_client = i.id_client)
group by c.id_client, c.name, i.due_date, i.payment_frequency

edited Jan 03 '19 at 11:47

answered Dec 29 '18 at 22:27

Derviş Kayımbaşıoğlu

28,492
4
50
72

1

thank you very much for your suggestion, it almost works perfectly but your query selects the highest value of "invoices.payment_frequency" from all records. The correct would be to select only the highest value of "invoices.due_date" and the corresponding "payment_frequency". Any idea? Thanks – Victor Benincasa Jan 02 '19 at 18:12
Now it works perfectly, thank you very much! – Victor Benincasa Jan 14 '19 at 20:58

MySQL - Join two tables by most recent date

1 Answers1