This has been bugging me for quite some time and have yet to come up with an elegant solution using strictly SQL.
Say for instance I have 2 tables, transactions, and statuses.
Transactions has a 1 to many relationship to statuses, meaning there can be multiple statuses for a single transaction.
So far simple, we can simply do
Code: Select all
SELECT * FROM transactions
INNER JOIN statuses ON statuses.transaction_id = transactions.id
GROUP BY transactions.id
..which should grab the latest status for each particular transaction. Now heres the problem: How can I acheive the same affect if I want to figure out how many transactions are on a particular status? In this example we search for status_type = 8 which means approved. However, if we have an approved transaction that was later canceled the following query will no longer work, since it will still return the approved status.
Code: Select all
SELECT * FROM transactions
INNER JOIN statuses ON statuses.transaction_id = transactions.id
WHERE statuses.status_type = 8
GROUP BY transactions.id
I hope that was clear. Thanks!