Select MySQL results based on most current row in join table

Question

I'm working with a third-party database of a vendor we use for our account management. One of the queries we need to run programmatically is to see which accounts are currently active. There is no single column for this -- status is tracked in a separate table from the main information table that tracks all changes in status, from activation to deletion.

I want to do a simple join like this:

SELECT  u.id ,
        s.status
FROM    user_table u
        JOIN status_table s ON u.id = s._id
WHERE   s.acct_status = "ACTIVE"
        OR s.new_status = "ACTIVE";

But this doesn't work because there might be a later record that sets the accounts status to TERMINATED or something else. Note: every account will have a status entry of one sort or another.

For the purposes of this question, it doesn't matter what the user table is like. The status table is very simple:

_id
date_entered
acct_status
new_status

I'm pretty sure that this query would get me the latest status update (thanks to this post, but I'm not sure how to throw in a join here:

select 
    *
from
    (select 
        _id, new_status
    from
        aria.get_acct_status_history
    order by date_entered desc) as t1
group by _id;

Any ideas?

M Khalid Junaid · Accepted Answer · 2014-07-30T22:04:49.443

If you need the latest record per user from your status table then you use a self join on data column by getting max date per user entries and i assume _id from status_table table refers to user id

SELECT s.* FROM status_table s
JOIN (
  SELECT _id ,MAX(date_entered) date_entered 
  FROM status_table 
  GROUP BY _id 
) s1
ON(s._id = s1._id  AND s.date_entered = s1.date_entered )
WHERE s.acct_status = "ACTIVE" or s.new_status = "ACTIVE";

Later on you and join you users table to get the user info,joining with max of date column ie. AND s.date_entered = s1.date_entered will satisfy your criteria to have recent row per user entries

SELECT u.id, s.status 
FROM user_table u 
JOIN status_table s ON u.id = s._id
JOIN (
  SELECT _id ,MAX(date_entered) date_entered 
  FROM status_table 
  GROUP BY _id 
) s1
ON(s._id = s1._id  AND s.date_entered = s1.date_entered )
WHERE s.acct_status = "ACTIVE" or s.new_status = "ACTIVE";

Select MySQL results based on most current row in join table

1 Answers1