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

Multi tool use
Multi tool use












1
















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.










share|improve this question















marked as duplicate by sticky bit, Shadow mysql
Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

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.























    1
















    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.










    share|improve this question















    marked as duplicate by sticky bit, Shadow mysql
    Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

    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.





















      1












      1








      1









      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.










      share|improve this question

















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      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 mysql
      Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

      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 mysql
      Users with the  mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.

      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.


























          1 Answer
          1






          active

          oldest

          votes


















          -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





          share|improve this answer





















          • 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 Answer
          1






          active

          oldest

          votes








          1 Answer
          1






          active

          oldest

          votes









          active

          oldest

          votes






          active

          oldest

          votes









          -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





          share|improve this answer





















          • 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














          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





          share|improve this answer





















          • 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








          -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





          share|improve this answer















          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






          share|improve this answer














          share|improve this answer



          share|improve this answer








          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














          • 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



          UdceC crHwDN0gZuAFfY GJ9Ku e
          Fo sAC WnK,KVX,1TKhyMntAfzFo,IE,04kdnEPXIb0SBs a0

          Popular posts from this blog

          Monofisismo

          Angular Downloading a file using contenturl with Basic Authentication

          Olmecas