Find most recent joined record in a many to many

Question

Run a SQL query which pulls up a list of invoices including its most recent transaction.

Requirements:

• get this information using 1 query (inner queries might be necessary)
• include invoices that do not have a transaction yet
• able to filter by fields on the invoice or transaction. example scenarios below:
  • example 1: all invoices whose last transaction status is settled
  • example 2: all invoices whose status is unpaid which do not have a last transaction)

The tricky part is that we are dealing with a many-to-many scenario. All StackOverflow posts I found were regarding one-to-many scenarios. The following SO post seemed to take me in the right direction, but could not quiet figure it out: SQL join: selecting the last records in a one-to-many relationship

The reason I have a many-to-many here is because a transactions can be part of many invoices. Basically several invoices can be assigned to the same payment profile, therefore we create one transaction (instead of having a separate transactions for each invoice). In addition, an invoice can have many transactions since transactions can be declined.

I know you may think, why not have an overarching invoice and then have the current invoices be invoice_lines, or something along those lines, but unfortunately the schema cannot be changed at this time.

Here is an outline my tables:

Any help would be great appreciated!

Answer 1

Take Bill Karwins approach to solve this kind of problems to find the most recent transaction for any invoice_id:

SELECT
    it.invoice_id,
    t1.*
FROM
    invoice_transaction it
INNER JOIN
    transaction t1
ON
    it.transaction_id = t1.id
LEFT OUTER JOIN
    transaction t2
ON
    (it.transaction_id = t2.id
AND
    (t1.created_at < t2.created_at OR t1.created_at = t2.created_at AND t1.id < t2.id))
WHERE
    t2.id IS NULL

gives you t1 as the most recent transaction for that invoice_id. So you've only got to join the invoice table to get the details of this invoice:

SELECT
    i.*
    it.invoice_id,
    t1.*
FROM
    invoice_transaction it
-- we can add our join to the invoice table here
INNER JOIN
    invoice i
ON
    it.invoice_id = i.id
-- and we're done
INNER JOIN
    transaction t1
ON
    it.transaction_id = t1.id
LEFT OUTER JOIN
    transaction t2
ON
    (it.transaction_id = t2.id
AND
    (t1.created_at < t2.created_at OR t1.created_at = t2.created_at AND t1.id < t2.id))
WHERE
    t2.id IS NULL

Now you can filter for invoices with a certain amount or those that are due on a certain date as you want by adding this to the WHERE clause.

That's what I meant it should not be too difficult to adapt the solution you did already find here at StackOverflow.