MySQL - Join two tables by most recent date [duplicate]

Multi tool use
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
Get records with max value for each group of grouped SQL results
17 answers
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.
mysql join
marked as duplicate by sticky bit, Shadow
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 29 '18 at 22:28
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
Get records with max value for each group of grouped SQL results
17 answers
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.
mysql join
marked as duplicate by sticky bit, Shadow
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 29 '18 at 22:28
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
Get records with max value for each group of grouped SQL results
17 answers
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.
mysql join
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
Get records with max value for each group of grouped SQL results
17 answers
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.
This question already has an answer here:
SQL select only rows with max value on a column
30 answers
Get records with max value for each group of grouped SQL results
17 answers
mysql join
mysql join
edited Jan 2 at 21:39
Victor Benincasa
asked Dec 29 '18 at 22:06
Victor BenincasaVictor Benincasa
83
83
marked as duplicate by sticky bit, Shadow
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 29 '18 at 22:28
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by sticky bit, Shadow
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Dec 29 '18 at 22:28
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
add a comment |
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
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
add a comment |
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
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 3 at 11:47
answered Dec 29 '18 at 22:27


SimonareSimonare
9,88411737
9,88411737
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
add a comment |
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
1
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 2 at 18:12
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 2 at 18:12
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
Now it works perfectly, thank you very much!
– Victor Benincasa
Jan 14 at 20:58
add a comment |
UdceC crHwDN0gZuAFfY GJ9Ku e