Nomi Khurram Nomi Khurram - 5 months ago 8
MySQL Question

How to find a last record for a record in first table in mysql

I have a first table of users which have a boolean field to represent a user as

active
and in the second table i have user
audits
.

I have to find when a user is activated last time, so that means i have many number of
audits
just have to get last activated
audit
for a currently
active
user.


Fields of User Table:


user_id, username, firstname, lastname, email, isactive



Fields of Audit Table:


user_id, audit_date, actiontype

Answer

I guess You want this:

SELECT 
  user_table.*, MAX(audit_table.audit_date) AS last_activated
FROM audit_table 
LEFT JOIN user_table ON (user_table.user_id = audit_table.user_id) 
WHERE 
  audit_table.user_id = 5 AND audit_table.actiontype = 'activate' 
  AND user_table.isactive = 1;

If You want list of activated users with last activation date:

SELECT 
  user_table.*, MAX(audit_table.audit_date) AS last_activated 
FROM audit_table 
LEFT JOIN user_table ON (user_table.user_id = audit_table.user_id) 
WHERE 
  audit_table.actiontype = 'activate' AND user_table.isactive = 1
GROUP BY audit_table.user_id;