I have some problem with some tasks.

I've got many tasks like this: Create a query that shows up for every client, ID of his last invoice (name, surname, ID of last invoice, invoice date)

Here are the tables:

Table "Clients":

Pesel is a polish person id

Table "Invoices":

I must do it using correlated subquery with aliases. I know how it works with comparing records from single table, but how to do it with two of them?

The example I've got from a teacher is like:

SELECT a.subject, a.grade, a.data
FROM students_grade AS a
WHERE data = ( 
               SELECT MAX( data )
               FROM students_grade AS b
               WHERE a.subject = b.subject );

And I need to do it simillar

Thanks for any kind of help :)

CodePudding user response：

it goes as per your example using 1 and 2 'invoices', and then joining 'clients':

select c.name, c.surname
     , i1.InvoiceID as `ID of last invoice`
     , i1.InvoiceDate as `invoice date`
from invoices i1
left join clients c on c.ClientID = i1.ClientID
where i1.InvoiceDate = ( select max(i2.InvoiceDate)
                         from invoices i2
                         where i2.ClientID = i1.ClientID)

dbfiddle for your sample data here (MariaDB 10.3)

CodePudding user response：

Doing a correlated subquery can be expensive. By pre-querying the max() invoice date per client and JOINING should be better performance.

select
      PQ.ClientID,
      c.SurName,
      c.Name,
      i.InvoiceID,
      i.InvoiceDate
   from
      ( select
              clientID,
              max( InvoiceDate ) MaxDate
           from
              invoices
           group by
              clientID ) PQ
         JOIN Invoices i
            on PQ.ClientID = i.ClientID
           AND PQ.MaxDate = i.InvoiceDate
         JOIN Client c
            on PQ.ClientID = c.ClientID