mysql - Find most recent joined record in a many to many -

mysql - Find most recent joined record in a many to many -

run sql query pulls list of invoices including recent transaction.

requirements:

get info using 1 query (inner queries might necessary) include invoices not have transaction yet able filter fields on invoice or transaction. illustration scenarios below: example 1: invoices lastly transaction status settled example 2: invoices status unpaid not have lastly transaction)

the tricky part dealing many-to-many scenario. stackoverflow posts found regarding one-to-many scenarios. next post seemed take me in right direction, not quiet figure out: sql join: selecting lastly records in one-to-many relationship

the reason have many-to-many here because transactions can part of many invoices. several invoices can assigned same payment profile, hence create 1 transaction (instead of having separate transactions each invoice). in addition, invoice can have many transactions since transactions can declined.

i know may think, why not have overarching invoice , have current invoices invoice_lines, or along lines, unfortunately schema cannot changed @ time.

here outline tables:

any help great appreciated!

take bill karwins approach solve kind of problems find recent transaction invoice_id:

select it.invoice_id, t1.* invoice_transaction inner bring together transaction t1 on it.transaction_id = t1.id left outer bring together transaction t2 on (it.transaction_id = t2.id , (t1.created_at < t2.created_at or t1.created_at = t2.created_at , t1.id < t2.id)) t2.id null

gives t1 recent transaction invoice_id. you've got bring together invoice table details of invoice:

select i.* it.invoice_id, t1.* invoice_transaction -- can add together our bring together invoice table here inner bring together invoice on it.invoice_id = i.id -- , we're done inner bring together transaction t1 on it.transaction_id = t1.id left outer bring together transaction t2 on (it.transaction_id = t2.id , (t1.created_at < t2.created_at or t1.created_at = t2.created_at , t1.id < t2.id)) t2.id null

now can filter invoices amount or due on date want adding where clause.

that's meant should not hard adapt solution did find here @ stackoverflow.

mysql sql database relational-database