Page 1 of 1
Latest row in many-to-one relationship
Posted: Tue Nov 11, 2008 6:18 pm
by John Cartwright
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!
Re: Latest row in many-to-one relationship
Posted: Tue Nov 11, 2008 10:35 pm
by novice4eva
In statuses table do we have any other column such as dates or anything that might point which is the latest status!!
Re: Latest row in many-to-one relationship
Posted: Tue Nov 11, 2008 11:00 pm
by califdon
Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 2:02 am
by VladSun
Jcart wrote: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.
I'm not sure it will get the latest status. It's very close to my "favorite" thread -
viewtopic.php?t=72439 (at least my profile says so

)
In fact, my experience (and my executed-1-minute-ago-query

) shows that in this case MySQL will put the row with the smallest transaction_id found... So, it's not so simple

Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 2:04 am
by VladSun
Another soon seen thread:
viewtopic.php?f=2&t=78122
You may also find this link interesting:
http://www.xaprb.com/blog/2006/12/07/ho ... up-in-sql/
My solution, considering that the greatest status_type is the last one:
[sql]SELECT statuses.status_type, count(statuses.status_type)FROM transactionsINNER JOIN statuses ON statuses.transaction_id = transactions.id AND statuses.status_type = ( SELECT MAX(_statuses.status_type) FROM statuses AS _statuses WHERE _statuses.transaction_id = transactions.id ) GROUP BY statuses.status_type[/sql]
Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 2:19 am
by VladSun
novice4eva wrote:In statuses table do we have any other column such as dates or anything that might point which is the latest status!!
I think the latest status is concidered the one with the gratest id or status_type. Maybe an additional "status-order" column would be better.
Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 2:02 pm
by John Cartwright
VladSun wrote:novice4eva wrote:In statuses table do we have any other column such as dates or anything that might point which is the latest status!!
I think the latest status is concidered the one with the gratest id or status_type. Maybe an additional "status-order" column would be better.
Yes I did have the sense to do so

. Unfortunately I would prefer to accomplish this with strictly SQL (simply because it is such a large project with many components), although I did consider this approach.
I will take a look at the links posted and examples posted, and as usual is much appreciated.
I'll let you guys know how it goes,
Thanks
Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 2:40 pm
by John Cartwright
Turned out to be exactly what I was looking for. Thanks!
Heres what I ended up with, for those of you following.
Code: Select all
SELECT * , MAX( statuses.status_id ) , COUNT( * ) AS numtransactions
FROM `cens_transactions` AS transaction
INNER JOIN cens_transaction_statuses AS statuses ON statuses.gateway_id = transaction.gateway_id
GROUP BY transaction.id
HAVING MAX( statuses.status_id ) = 8
Re: Latest row in many-to-one relationship
Posted: Wed Nov 12, 2008 6:03 pm
by VladSun
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?
Sorry, I've thought you need the count of transactions per last status

In your case, califdon's solution is the right one
Anyway you should change your first query:
[sql]SELECT * FROM transactionsINNER JOIN statuses ON statuses.transaction_id = transactions.idGROUP BY transactions.id[/sql]
to a query similar to the one I suggested.